0

Looking for some help on this issue.

I have a table that is a list of driver information and I am in need of selecting the rows that have the max report_date value. Also, a driver can be in there multiple times for each report_date so I need distinct drivers.

How can I query that?

  • 1
    possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) – Florin Ghita Sep 17 '15 at 13:00

2 Answers2

1

This will get the row (or rows) for each driver which have the latest report date for each driver.

SELECT *
FROM   (
  SELECT di.*,
         DENSE_RANK() OVER ( PARTITION BY driver ORDER BY report_date DESC ) AS rnk
  FROM   driver_information di
)
WHERE  rnk = 1;

If you only want a single row per driver (rather than all rows for each driver which have the latest date) then change DENSE_RANK to ROW_NUMBER.

If you only want those rows which are on the latest date then you can remove the PARTITION BY driver clause.

MT0
  • 143,790
  • 11
  • 59
  • 117
0

Something like:

select distinct driver
from mytable
where report_date = (select max(report_date) from mytable);
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • yep i ended up using something like this. forgot to check back in here after I resolved it myself. –  Sep 18 '15 at 19:24