Q
Problem solve Get help with specific problems with your technologies, process and projects.

Extracting the highest record in a group

I need to extract the current medical reserve for each case_no. Medical reserve is in colum5, case_no is composite of colum1, colum2 and colum3. The most current med_reserve is the highest rec_no for each case_no. For instance, in the example below the case_no 011 99 75, with rec_no 2 shows the most current medical reserve for this case_no. There are case_no with only one rec_no, others with 10, 15 or more rec_no. What would my select statement look like so I can get the case_no with the highest rec_no only?

colum1 colum2 colum3 rec_no    colum5
010     99     120    0        10.00
011     99     75     0        15.00
011     99     75     1        10.00
011     99     75     2         5.00
040     01     131    0        25.00
040     01     131    1        15.00
012     00     245    0         9.00


The solution requires a correlated subquery to find the highest rec_no for the group of rows that have the same case_no Primary Key --

select colum1, colum2, colum3, rec_no, colum5 
  from yourTable XX
 where rec_no = 
       ( select max(rec_no)
           from yourtable
          where colum1 = XX.colum1
            and colum2 = XX.colum2
            and colum3 = XX.colum3
       )

This solution is similar to How to get the maximum value for a row (18 April 2001) with a slight twist, namely that the PK is a composite key, so you simply test for equality on all three parts of the key.


This was last published in March 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close