CODEWITHSUNDEEP
mysqlsql-server
yogeshkmrsoni
yogeshkmrsoni

Reputation: 315

how to select latest records from a table

DB : Mysql

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

enter image description here

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

result should be:

enter image description here

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.

Upvotes: 0

Views: 328

Answers (4)

Smart003
Smart003

Reputation: 1119

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

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

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

Upvotes: 2

Adit Shah
Adit Shah

Reputation: 100

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

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

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)

Upvotes: 0

Related Questions