I have the following table:
DOA_MAINT
SR_NO ITEM LINE_NO TYPE
100 A10 1 98
100 A10 2 94
200 A20 1 77
200 A20 2 98
200 A20 3 56
I need to result as:
SR_NO ITEM LINE_NO TYPE
100 A10 2 94
200 A20 3 56
for every SR_NO, to bring one record based on LINE_NO field.
One typical route is to establish an incrementing counter in order of descending column_you_want_to_max_by, which restarts every column_that_defines_the_group. You can then select only those rows with counter=1
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY sr_no ORDER BY line_no DESC) rn
) x
WHERE rn = 1
This way returns a random single line_no if there are two with the same max value. There are other ways, such as doing a classic max line_no/groupby sr_no as a subquery and joining it back in, which will return both rows if there are two line_no that are the max
