SQL query for getting only the latest data

Question

I would like to make a SQL query where I am only able to see my latest shipments. Currently I get the following result.

item    date        licence plate number
1       13.5.2016   abc-123
2       13.5.2016   abc-123
3       10.5.2016   xyz-567
1       20.4.2016   abc-123
2       20.4.2016   abc-123
6       10.5.2016   xyz-567

However I would like to only get the latest shipments based on the licence plate number. The wanted output would look like

item    date        licence plate number
1       13.5.2016   abc-123
2       13.5.2016   abc-123
3       10.5.2016   xyz-567

Define "latest", then we can help you. What makes item 3 included, but item 6 excluded? — Andreas, May 13 '16 at 05:30
Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Clockwork-Muse, May 13 '16 at 06:11

score 3 · Answer 1 · answered May 13 '16 at 05:33

You can use the following query:

SELECT t1.*
FROM mytable AS t1
JOIN (
   SELECT item, MAX("date") AS "date"
   FROM mytable
   GROUP BY item
) AS t2 ON t1.item = t2.item AND t1."date" = t2."date"

The query uses a derived table which selects the latest date per item. Using this derived table we can select the record that corresponds to this date.

score 1 · Answer 2 · answered May 13 '16 at 06:08

The following is standard SQL supported by a wide range of DBMS:

select item, date, licence_plate_number
from (
    select item, date, licence_plate_number, 
           row_number() over (partition by licence_plate_number order by date desc as rn)
    from the_table
) t
where rn = 1
order by item;

Using a window function is typically faster than a self join with an aggregate.

score 0 · Answer 3 · edited May 13 '16 at 05:48

0

Maybe this helps:

SELECT TOP 10 --change 10 to any desired number to be viewed 
*
FROM YourDatabaseName 
ORDER BY Date DESC

edited May 13 '16 at 05:48

MusicLovingIndianGirl

5,909
9
34
65

answered May 13 '16 at 05:44

Max Fesenko

123
1
7

Not what OP is asking for. (And product specific answer to a question with no dbms tagged.) – jarlh May 13 '16 at 06:58

Sandeep Kumar · Answer 4 · 2016-05-13T08:29:10.203

0

You have to first find our the max date of each plate number then join the tables.

You can use below query.

select t2."item",t1."date",t1."licence plate number" from (
(select max("date") as date, "licence plate number" from Table_1 group by 
"licence plate number")) t1
 left join Table_1 t2 
on (t1."date" =  t2."date" and t1."licence plate number" =  t2."licence plate number")

The output of this query will be.

1      2016-05-13      abc-123
2      2016-05-13      abc-123
3      2016-05-10      xyz-567
6      2016-05-10      xyz-567

edited May 13 '16 at 08:29

answered May 13 '16 at 06:03

Sandeep Kumar

1,172
1
9
22

Note that square brackets for delimited identifiers is product specific, and the question has no dbms tagged. ANSI SQL has double quotes, e.g. `"item"`. (MS SQL Server actually supports both versions.) – jarlh May 13 '16 at 07:02

SQL query for getting only the latest data

4 Answers4