Oracle select most recent values by date

Question

I have a number of database records for experiments taken a number of weeks apart from each other, usually approximately 2-3 weeks apart. The goal is to select the most recent results by date to generate a report. Here is an example dataset. For this dataset the goal is to pick the last row since it is the most recent measurement taken for the QL_ID P14404.

QL_ID is the primary key and QL_VAL are the measurements for the sample and MEASUREMENT_DATE is the date the measurements were taken. The table name is QL_RESULTS. I am able to get the most recent date and the QL_ID by using the query below, but I'm stuck on how to get all 3 columns i.e. QL_VAL,QL_ID,MEASUREMENT_DATE.

SELECT QL_ID,MAX(MEASUREMENT_DATE) FROM QL_RESULTS
GROUP BY QL_ID

  QL_VAL  QL_ID       MEASUREMENT_DATE 
  25      P14404        01-JUL-10
  30      P14404        15-JUL-10
  21      P14404        29-JUN-10

I saw numerous solutions but known was exactly what I was looking for. — BreenDeen, Jun 01 '17 at 11:35

score 2 · Accepted Answer · answered Jun 01 '17 at 09:00

You could rank your rows per ql_id according to the measurement date:

SELECT ql_val, ql_id, measurement_date
FROM   (SELECT ql_val, ql_id, measurement_date,
               RANK() OVER (PARTITION BY ql_id
                            ORDER BY measurement_date DESC) AS rk 
        FROM   ql_results) t
WHERE  rk = 1

score 2 · Answer 2 · answered Jun 01 '17 at 09:20

2

I think the slim solution is this one:

SELECT QL_ID, MAX(MEASUREMENT_DATE), 
   MAX(QL_VAL) KEEP (DENSE_RANK LAST ORDER BY MEASUREMENT_DATE) as QL_VAL
FROM QL_RESULTS
GROUP BY QL_ID;

answered Jun 01 '17 at 09:20

Wernfried Domscheit

46,769
7
65
91

Thanks a bunch! Also works, It'd be nice if I could accept both solutions. – BreenDeen Jun 01 '17 at 11:33
@BreenDeen, you can at least make an upvote :-) – Wernfried Domscheit Jun 02 '17 at 05:53

Oracle select most recent values by date

2 Answers2