SELECT newest rows, ignore old duplicates

Question

In my table, i have the following columns :

CRMID | user | ticket_id | | description | date | hour

what i am trying to do is to select all the rows from the table, but when two (or more) rows have the same ticket_id, i want only the newest one to appear in the results, so the row with the newest date and hour.

the problem here is that i should be addin cases, if the values from the date column are the same, then i will compare the hour colum, otherwise, its simple cauz i'll be comparing only the date column.

possible duplicate of [Get records with highest/smallest per group](http://stackoverflow.com/questions/8748986/get-records-with-highest-smallest-whatever-per-group) — Marcus Adams, Apr 22 '14 at 13:40

potashin · Answer 1 · 2014-04-22T13:58:44.330

2

  SELECT 
    n.*
FROM 
    table n RIGHT JOIN  (
       SELECT 
         MAX(date) AS max_date,
         (SELECT MAX(hour) AS hour WHERE date = max_date) AS hour,
         user,
         ticket_id 
       FROM
         table
       GROUP BY
         user,
         ticket_id
       ) m ON n.user = m.user AND n.ticket_id = m.ticket_id

edited Apr 22 '14 at 13:58

answered Apr 22 '14 at 13:26

potashin

44,205
11
83
107

1

This looks alot better than mine! :) – Matt Apr 22 '14 at 13:34
It's not working, here is my querry till now select distinct t.ticket_id, t.id from tickets t where not exists (SELECT 1 from tickets t1 where t.date < t1.date and t.ticket_id = t1.ticket_id ) – PaxBin Apr 22 '14 at 13:38
what i still need id the hour condition – PaxBin Apr 22 '14 at 13:38
Here is the working query : SELECT DISTINCT t.ticket_id, t.id FROM tickets t WHERE NOT EXISTS ( SELECT 1 FROM tickets t1 WHERE ( t.date < t1.date OR ( t.date = t1.date AND t.heure < t1.heure ) ) AND ( t.ticket_id = t1.ticket_id ) ) – PaxBin Apr 22 '14 at 13:51

score 1 · Answer 2 · answered Apr 22 '14 at 14:27

You may want to combine your date and hour columns, then perform the comparison

SELECT foo.*
FROM foo
JOIN (SELECT ticket_id, MAX(ADDTIME(`date`,`hour`)) as mostrecent
      FROM foo
      GROUP BY ticket_id) AS bar
ON bar.ticket_id = foo.ticket_id
  and bar.mostrecent = ADDTIME(foo.`date`,foo.`hour`);

SELECT newest rows, ignore old duplicates

2 Answers2