Group by by two fields ordered by a third

Asked

Viewed 805 times

2

I have the following data:

|--------------------------------------------------------|
|USER   | ID    | DEVICEID          | DATE               |
|--------------------------------------------------------|
|7      | 14450 | aa6603ceef3b397c  | 2016-08-03 10:19:14|
|--------------------------------------------------------|
|7      | 14457 | aa6603ceef3b397c  | 2016-08-02 18:33:43|
|--------------------------------------------------------|
|7      | 14398 | 82c422272772e05a  | 2016-06-07 09:56:55|
|--------------------------------------------------------|
|7      | 14399 | 82c422272772e05a  | 2016-06-07 09:14:28|
|--------------------------------------------------------|
|249    | 14433 | 75da16fe9c4e480d  | 2016-07-06 11:10:32|
|--------------------------------------------------------|
|249    | 14358 | 75da16fe9c4e480d  | 2016-08-03 10:03:07|
|--------------------------------------------------------|

But I need to mount a query to get the following data:

|--------------------------------------------------------|
|USER   | ID    | DEVICEID          | DATE               |
|--------------------------------------------------------|
|7      | 14450 | aa6603ceef3b397c  | 2016-08-03 10:19:14|
|--------------------------------------------------------|
|7      | 14398 | 82c422272772e05a  | 2016-06-07 09:56:55|
|--------------------------------------------------------|
|249    | 14358 | 75da16fe9c4e480d  | 2016-08-03 10:03:07|
|--------------------------------------------------------|

That is, I need to group by Usere Deviceid, taking whatever has the latest date, but I need to know his Id too, because I need more data that exists in this table and the idea was to use as subquery. How could I do that?

sql database sql-server group-by

2 answers

2


I don’t have SQL Server installed, but I tested with Mysql and you should be able to with a similar SQL:

SELECT t2.* FROM
    (SELECT user, deviceid, MAX(date) AS date FROM q144255
     GROUP BY user, deviceid) t1
LEFT JOIN q144255 t2
    ON t1.user = t2.user AND t1.deviceid = t2.deviceid AND t1.date = t2.date;

Upshot:

+------+-------+------------------+---------------------+
| user | id    | deviceid         | date                |
+------+-------+------------------+---------------------+
|    7 | 14398 | 82c422272772e05a | 2016-06-07 09:56:55 |
|    7 | 14450 | aa6603ceef3b397c | 2016-08-03 10:19:14 |
|  249 | 14358 | 75da16fe9c4e480d | 2016-08-03 10:03:07 |
+------+-------+------------------+---------------------+

EDIT:

SELECT DISTINCT t1.user, t1.deviceid, t1.id, t1.date
FROM q144255 t1
WHERE t1.date = (SELECT MAX(t2.date) FROM q144255 t2
                 WHERE t2.user = t1.user and t2.deviceid = t1.deviceid);

  • Thanks for the answer, I did something similar here but with the reverse thought of yours. I am only suspicious of having to use this comparison in LEFT JOIN without having the ID as a reference, because there is a very small (almost zero) chance that there are two records with the same date.

  • 1

    I get it. Try it the way I reported it in the reply issue.

  • Thanks, it helped a lot!

0

You can use a JOIN with a subselect on it, as follows.

DECLARE @TABELA TABLE
(
   USER_ INT,
   ID INT,
   DEVICEID VARCHAR(50),
   DATE_ DATETIME
)

INSERT INTO @TABELA VALUES 

(
7 ,      14450 , 'aa6603ceef3b397c'   ,'2016-08-03 10:19:14')
,(
7    ,   14457 , 'aa6603ceef3b397c'   ,'2016-08-02 18:33:43')
,(
7   ,    14398 , '82c422272772e05a'   ,'2016-06-07 09:56:55')
,(
7    ,   14399 , '82c422272772e05a'   ,'2016-06-07 09:14:28')
,(
249  ,   14433 , '75da16fe9c4e480d'   ,'2016-07-06 11:10:32')
,(
249  ,   14358 , '75da16fe9c4e480d'   ,'2016-08-03 10:03:07')


SELECT * FROM @TABELA T1
JOIN (
        SELECT USER_,  DEVICEID, MAX(DATE_) AS DATE_ FROM @TABELA           
        GROUP BY USER_, DEVICEID
     ) T2
ON T2.USER_ = T1.USER_
AND T2.DEVICEID = T1.DEVICEID 
AND T2.DATE_ = T1.DATE_

Browser other questions tagged sql database sql-server group-by

You are not signed in. Login or sign up in order to post.