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



Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.