I need to extract the current medical reserve for each case_no. Medical reserve is in colum5, case_no is composite...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.