how to select latest records from a table

Question

DB : Mysql

I am having a table (tblSomething) with following records:

Now i need to filter this records by selecting the latest unique records from this table.

result should be:

In 2nd image the joe and rina with 11/16/2015 is not available.

Can you tell me how to write a query to achieve this.

@DarkKnight Actually, it _could_ be both, if we give a pure ANSI-92 query as an answer. But yeah, he should label appropriately. — Tim Biegeleisen, Dec 09 '15 at 06:33
Why do the records `404 468 joe tina` appear twice in the result set? Should the `11/16/2015` records not be getting filtered out by the query? — Tim Biegeleisen, Dec 09 '15 at 07:14
@TimBiegeleisen because the uniqueness is based on ID1 and ID2 column with latest date row. — yogeshkmrsoni, Dec 09 '15 at 07:15
This doesn't make sense. There should only be _one_ `404 468 joe tina` record in the result set. Why was the filter not applied here, but was applied for the `404 467 joe rina` record? — Tim Biegeleisen, Dec 09 '15 at 07:16

Tim Biegeleisen · Accepted Answer · 2015-12-09T07:34:38.367

2

Try the following query:

SELECT t1.ID1, t1.ID2, t1.Name1, t1.Name2, t1.date
FROM tblSomething t1
INNER JOIN
(
    SELECT ID1, ID2, MAX(date) AS date
    FROM tblSomething
    GROUP BY ID1, ID2
) t2
ON t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2 AND t1.date = t2.date

The inner query determines, for each ID1/ID2 pair, the most recent date. Then a query on the entire tlbSomething table is restricted by retaining only those records which have this most recent date for each ID1/ID2 pair.

Click the link below for a running demo using the sample data from the OP.

SQLFiddle

edited Dec 09 '15 at 07:34

answered Dec 09 '15 at 06:32

Tim Biegeleisen

502,043
27
286
360

plus one for simple solution and explanation. – Sateesh Pagolu Dec 09 '15 at 06:44
nice solution, what if it is having more fields, how will you handle this, see updated question. – yogeshkmrsoni Dec 09 '15 at 07:07
@yogeshkmrsoni Give the Fiddle a try. The output matches what you showed us in the OP. – Tim Biegeleisen Dec 09 '15 at 07:36

score 0 · Answer 2 · answered Dec 09 '15 at 06:34

0

Use MAX or str_to_date

SELECT *
FROM table t1
WHERE  str_to_date(t1.date,'%m/%d/%Y')=(SELECT MAX(str_to_date(t2.date,'%m/%d/%Y'))
              FROM table t2
              WHERE t1.ID2 = t2.ID2)

answered Dec 09 '15 at 06:34

Abhishek Sharma

6,689
1
14
20

score 0 · Answer 3 · edited May 23 '17 at 10:27

0

Try this :

 select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

Reference : how do I query sql for a latest record date for each user

edited May 23 '17 at 10:27

Community

1
1

answered Dec 09 '15 at 06:35

Adit Shah

100
9

score 0 · Answer 4 · answered Dec 09 '15 at 13:37

CREATE TABLE #tblSomething
(ID1 int, ID2 int, Name1 varchar(55), Name2 varchar(55), date1 date, cost int, days int, somefield int);

INSERT INTO #tblSomething
    (ID1, ID2, Name1, Name2, date1, cost, days, somefield)
VALUES
    (330, 435, 'sn1', 'hello1', '2015-11-17', 500, 12, 34),
    (404, 467, 'joe', 'rina',   '2015-11-23', 600, 23, 22),
    (404, 467, 'joe', 'rina',   '2015-11-16', 700, 11, 123),
    (404, 468, 'joe', 'tina',   '2015-11-23', 800, 23, 41),
    (404, 468, 'joe', 'tina',   '2015-11-16', 789, 11, 43);

try this

with res as (select *,ROW_NUMBER() over(partition by ID1, ID2, Name1, Name2 order by ID1, ID2, Name1, Name2) as rn from #tblSomething)
    select ID1, ID2, Name1, Name2, date1, cost, days, somefield from res where rn=1

output

how to select latest records from a table

4 Answers4