Get most recent record from MySQL - this have many duplicate records

Question

I am trying to fetch the most recent record from my DB. The table looks like the below.

Ticket_id|  date|   comments
EWU-752-84170|  4/10/2015 13:26|bla
HCX-943-86125|  4/10/2015 13:39|    ola
IKW-626-96314|  4/10/2015 13:42|    jkj
EWU-752-84170|  4/10/2015 13:28|    blo
EWU-752-84170|  4/10/2015 13:37|    ala
HCX-943-86125|  4/10/2015 15:11|    kbdkj
EWU-752-84170|  4/10/2015 13:43|    cla

and the output should send the most recent records as below.

Ticket_id|  date|   comments
EWU-752-84170|  4/10/2015 13:43|    cla
HCX-943-86125|  4/10/2015 15:11|    kbdkj
IKW-626-96314|  4/10/2015 13:42|    jkj

@hellcode that returns a single record, o/p should remove all the duplicate ticket_id and show the most recent records against ticket_id — Pradeep Vempati, Apr 10 '15 at 18:33
possible duplicate of [php mysql Group By to get latest record, not first record](http://stackoverflow.com/questions/13459516/php-mysql-group-by-to-get-latest-record-not-first-record) — Sean, Apr 10 '15 at 18:52

score 0 · Answer 1 · answered Apr 10 '15 at 18:31

0

ORDER BY date DESC limit 1

That should work fine.

answered Apr 10 '15 at 18:31

Mike

103
1
2
11

this will give an top most record, but the problem is i need to remove all the duplicates from the table – Pradeep Vempati Apr 10 '15 at 18:35
Isn't that what you asked? "Get most recent record from MySQL" – Mike Apr 10 '15 at 18:37
apologies for my typo... i need to remove all the duplicates from the table and the time stamp should be the latest.... – Pradeep Vempati Apr 10 '15 at 18:46

score 0 · Answer 2 · answered Apr 10 '15 at 18:38

0

SELECT t1.*
FROM
  mytable t1 LEFT JOIN mytable t2
  ON
    t1.Ticket_id=t2.Ticket_id
    AND t1.date<t2.date
WHERE
  t2.date IS NULL

answered Apr 10 '15 at 18:38

fthiella

48,073
15
90
106

score 0 · Accepted Answer · answered Apr 10 '15 at 19:00

0

This will select all tickets where another ticket with a later date does not exist (therefore it selects only the latest tickets)

select * from mytable t1
where not exists (
    select 1 from mytable t2
    where t2.ticket_id = t1.ticket_id
    and t2.date > t1.date
)

answered Apr 10 '15 at 19:00

FuzzyTree

32,014
3
54
85

thanks Fuzzy... this is working good.. since i have many duplicate records i just modified select 1 to select *.... – Pradeep Vempati Apr 10 '15 at 20:07

score 0 · Answer 4 · answered Apr 10 '15 at 20:10

0

    select * from dailyhandover t1
where not exists (
    select * from dailyhandover t2
    where t2.ticket_id = t1.ticket_id
    and t2.date > t1.date
) and customer_name='bata'

answered Apr 10 '15 at 20:10

Pradeep Vempati

66
10

Get most recent record from MySQL - this have many duplicate records

4 Answers4