selecting unique rows in desc order

Question

i want to select the last row inserted by each id order by time DESC

s.no     id    message           status     time
1        3     this is msg 1     unread     100001
2        2     this is msg 2     read       100002
3        4     this is msg 3     read       100003
4        3     this is msg 4     unread     100004
5        2     this is msg 5     read       100005
6        3     this is msg 6     unread     100006

i am using

select * from table group by id order by MAX(time) DESC

it is giving the right id sequence but rows are interchanged

i want it like :

s.no     id    message           status     time
6        3     this is msg 6     unread     100006
5        2     this is msg 5     read       100005
3        4     this is msg 3     read       100003

help me plz

I thought you accepted an answer on [this](http://stackoverflow.com/questions/19334128/selecting-distinct-value-order-by-desc). — geomagas, Oct 13 '13 at 11:47
@MillerKoijam - It is in the question. Following the phrase "i want it like :" — Martin Smith, Oct 13 '13 at 11:51

Laurence · Answer 1 · 2013-10-13T11:53:21.703

You can pull out the max time as a separate subquery, and then join it back on to the table. This will return multiple rows for an id if it has a duplicate max time.

select 
    t.s.no,
    t.id
    t.message,
    t.status,
    t.time
from 
    table t
        inner join (
        select
            id,
            max(time) maxtime
        from
            table
        group by
            id
   ) mt
       on mt.id = t.id and t.time = mt.maxtime
order by
    t.time desc

score 1 · Answer 2 · answered Oct 13 '13 at 11:54

1

Try this:

select * from `table` t
join (
  SELECT id, MAX(`time`) max_t
  FROM `table`
  GROUP BY id
  ) m
on (t.id=m.id and t.`time`=m.max_t)
order by m.max_t desc

answered Oct 13 '13 at 11:54

geomagas

3,230
1
17
27

selecting unique rows in desc order

2 Answers2