Group by two fields ordered by a third party

1

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 what has the latest date, but I need to know the Id of it too, because I need more data that exists in this table and the idea was to use as a subquery. How could I do that?

    
asked by anonymous 03.08.2016 / 15:34

2 answers

2

I do not have SQL Server installed, but I tested it with MySQL and you should get it 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;

Result:

+------+-------+------------------+---------------------+
| 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);
    
03.08.2016 / 15:53
0

You can use a JOIN with a subselect in 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_
    
03.08.2016 / 20:57