Using SQL to select the unique record based from the max value of two rows

Question

I have a table where I store device id's, message, date, and time.

I am able to query and get all the records without any problem, however, what I want to do is to just get the latest message from each device. So for example, my database would look like:

device id     msg             date           time
Device 0 -- Message 0 -- 2016 - 05 - 22 -- 08:00:00
Device 1 -- Message 1 -- 2016 - 05 - 22 -- 09:00:00
Device 0 -- Message 2 -- 2016 - 05 - 23 -- 10:00:00
Device 1 -- Message 3 -- 2016 - 05 - 23 -- 11:00:00
Device 0 -- Message 4 -- 2016 - 05 - 24 -- 17:00:00
Device 1 -- Message 5 -- 2016 - 05 - 24 -- 16:00:00

And I want the result to be:

Device 0 -- Message 4 -- 2016 - 05 - 24 -- 17:00:00
Device 1 -- Message 5 -- 2016 - 05 - 24 -- 16:00:00

So far I could only sort by one column, whenever I try filtering through 2 columns at the same time using a join or a select within a select, I couldn't seem to make it work.

what is your rdbms? different db will need different solutions — Juan Carlos Oropeza, May 24 '16 at 15:11
Which dialect of SQL? Hover your cursor over your SQL tag and you'll see there are many different variations... — MatBailie, May 24 '16 at 15:11
Please [edit] your question to show the definition of your table. — O. Jones, May 24 '16 at 15:15

score 1 · Answer 1 · answered May 24 '16 at 15:16

1

Dialects with access to ROW_NUMBER() have the best approach.

WITH
  sorted AS 
(
  SELECT
    yourTable.*,
    ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY date_time_field DESC)  AS ordinal
  FROM
    yourTable
)
SELECT
  *
FROM
  sorted
WHERE
  ordinal = 1

Others have to jump through more hoops, first to find the latest date per device, then find re-find the corresponding row(s).

SELECT
  yourTable.*
FROM
  yourTable
INNER JOIN
(
  SELECT
    device_id,
    MAX(date_time_field)  AS max_date_time
  FROM
    yourTable
)
  latest
    ON  latest.device_id     = yourTable.device_id
    AND latest.max_date_time = yourTable.date_time_field

answered May 24 '16 at 15:16

MatBailie

83,401
18
103
137

Actually, as I understand it `date` and `time` are two different columns. – sagi May 24 '16 at 15:17
@sagi - then the op has more problems than I wish to discuss in the scope of my answer ;) – MatBailie May 24 '16 at 15:18
@sagi That doesn't really change anything besides the fact that the OP will have to produce a proper datetime value from the date and time. The basic solution stays the same. – jpw May 24 '16 at 15:21
Well, of course it does. How do you know OP will know how to do such thing? Or will even under stand he needs to? A little explanation could work it out @jpw – sagi May 24 '16 at 15:23
@sagi Fair enough. – jpw May 24 '16 at 15:24

score 1 · Answer 2 · answered May 24 '16 at 15:16

1

Well, you didn't tag your DBMS, so if its not MySQL this should work on almost any other DBMS :

SELECT * FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.device_id ORDER BY t.date DESC, t.time DESC) as rnk
    FROM YourTable t
) s 
WHERE s.rnk = 1

answered May 24 '16 at 15:16

sagi

40,026
6
59
84

The question was tagged, eventually, as MySQL. – halfer Sep 09 '18 at 13:30

Iłya Bursov · Answer 3 · 2016-05-24T15:22:34.710

0

you can something like this:

select a.* from
table as a
inner join (
    select device_id, max(concat(date,time)) as mdate
    from table
    group by device_id
) as b
on (a.device_id=b.device_id and concat(a.date,a.time)=b.mdate)

but, this approach will work only if all dates for given device id are unique

if you have some kind of auto increment id in this table, you can change it to:

select * from table
where id in (
    select max(id)
    form table
    group by device_id)

edited May 24 '16 at 15:22

answered May 24 '16 at 15:16

Iłya Bursov

23,342
4
33
57

@sagi can you elaborate? – Iłya Bursov May 24 '16 at 15:18
First solution : What if there are two messages in the same day but with different time?. Second solution: By his data sample, there isn't an auto increment id. – sagi May 24 '16 at 15:21

score -2 · Answer 4 · edited May 24 '16 at 15:31

-2

select * from table where deviceid in (SELECT device id ,MAX(date),max(time)
group by device id)

edited May 24 '16 at 15:31

Munawir

3,346
9
33
51

answered May 24 '16 at 15:17

Walter

1
1
3

Using SQL to select the unique record based from the max value of two rows

4 Answers4