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.
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.