CODEWITHSUNDEEP
sqloracle-databasemax
Imran Hemani
Imran Hemani

Reputation: 629

Oracle SQL - Get max based on Column

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.

Upvotes: 0

Views: 33

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

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

Upvotes: 2

Related Questions