asked
Only set maximum value in a column in oracle
I have table that has a column that may have same values in a burst. Like this: id | Col1 | +----+---------+ | 3 | 6050000 | +----+---------+ | 3 | 6050000 | +----+---------+ | 3 | 6050000 | +----+---------+ | 8 | 6060000 | +----+---------+ | 8 | 6060000 | +----+---------+ | 8 | 6060000 | +----+---------+ | 8 | 6060000 | +----+---------+ | 8 | 6060000 | +----+---------+ | 9 | 6050000 | +----+---------+ | 10 | 6000000 | +----+---------+ | 11 | 6000000 | +----+---------+ Now I want to prune rows where the value of Col1 is repeated and only select the Maximum id. For the above table the result should be: +----+---------+ | id | Col1 | +----+---------+ | 1 | 6050000 | +----+---------+ | 2 | 6050000 | +----+---------+ | 3 | 6050000 | +----+---------+ | 4 | 6060000 | +----+---------+ | 5 | 6060000 | +----+---------+ | 6 | 6060000 | +----+---------+ | 7 | 6060000 | +----+---------+ | 8 | 6060000 | +----+---------+ | 11 | 6070000 | +----+---------+ | 11 | 6070000 | +----+---------+ | 11 | 6070000 | +----+---------+ Using Dynamic values...
Based on your description, I believe your example sets are a little mixed up. Nevertheless, I believe the trick is to use what Oracle calls "analytic functions" and SQL Server calls "window partition functions." SELECT Id, Col1 , MAX(Id) OVER (PARTITION BY Col1) AS MaxId Use that in your query to compare Id vs. MaxId and see if the latter gives you what you want.
18 People are following this question.
