How to group by in SQL by largest date (Order By a Group By)

Question

I have the following database table

Here is my sample data I have for it.

What I am trying to figure out how to do is how to write a query to select all apntoken for userid='20' grouping by deviceid and then by apntoken as well (except that it should show the most recent apntoken).

Some queries I have tried are this.

SELECT 
   DISTINCT apntoken,deviceid,created 
FROM `distribution_mobiletokens` 
WHERE userid='20' 
GROUP BY deviceid

This returns the following result.

Notice the date is not the newest date. I added a fake entry called 'latestone' for apntoken where I set its date into the future.

Another query I tried is this.

SELECT 
   DISTINCT apntoken,deviceid,created 
FROM `distribution_mobiletokens` 
WHERE userid='20' 
GROUP BY deviceid,apntoken

Problem with this one however is I have extra data for the deviceid now since its not unique anymore?

Refer to picture

I'm not sure how to modify this query to do this or if this is easily possible?

For example I should with the data I have get back only '2' fields. One for deviceid 5628CF60-D0CF-479A-A944-A3496E085FC8 & 948D9BAD-B164-4830-ACEB-08A089A80558 like in the picture for the first query I tried but it should show the apntoken that says 'latestone'

The reason I need this is so I always send the 'newest' tokens to apple for push notifications for a particular device. Everytime a user logs in the device is the same but the apntoken can be different. So I figured if I can take the newest apntoken from the device for a user this will do the trick.

OTHER Queries I've tried

SELECT apntoken,deviceid,created 
FROM `distribution_mobiletokens` 
WHERE userid='20' 
GROUP BY deviceid ORDER BY created DESC

Notice it does not show latestone which should show up with its date

I forgot to put that I tried that but that won't work for me will edit question — Joseph Astrahan, Mar 28 '17 at 19:00
The group by is used for aggregation function as min , count .. i don't see any aggregation function in your query .. if you need unique rows you can use distinct ..(but distinct and group by have no sense) — ScaisEdge, Mar 28 '17 at 19:02
I recently modified question to make it more clear, let me know if that helps at all, basically I need to somehow do an Order By on a Group By for deviceid, where it orders the grouping by createdDate newest — Joseph Astrahan, Mar 28 '17 at 19:04
good idea, I tried SELECT DISTINCT apntoken,deviceid,created FROM `distribution_mobiletokens` WHERE userid='20' GROUP BY deviceid ORDER BY created ASC, and DESC variation but returned same result — Joseph Astrahan, Mar 28 '17 at 19:06
I modified question to show results when I tried orderby, still no luck — Joseph Astrahan, Mar 28 '17 at 19:09
I think this should work GROUP BY deviceid,created ORDER BY created DESC — Sandeep, Mar 28 '17 at 19:18

score 1 · Accepted Answer · answered Mar 28 '17 at 19:06

1

My favorite way to construct this SQL is to use a not exists clause like so:

SELECT apntoken,deviceid,created 
FROM `distribution_mobiletokens` as dm
WHERE userid='20'
and not exists (
    select 1 
    from `distribution_mobiletokens`
    where userid = '20'
    and deviceid = dm.deviceid
    and created > dm.created
    )

answered Mar 28 '17 at 19:06

Anand

1,165
10
18

2

Of course :) the not exists clause is stating that any rows with the same userid (=20) and deviceid as the original table (alias dm) but having a later created date should not exist in the results of the select. Leaving only the latest rows for each userid=20 and deviceid. Voila! – Anand Mar 28 '17 at 19:14
search the internets for "correlated subquery"; lots of literature on it – user1327961 Mar 28 '17 at 19:15
Thanks for the answer to my question and the information, I can see I have a lot of reading to do on this subject :). – Joseph Astrahan Mar 28 '17 at 19:15
what is purpose of select 1 in this query btw? – Joseph Astrahan Mar 28 '17 at 19:57
The ```not exists``` clause looks for the existence of records, it doesn't matter what the contents of the records are. You can select a column, 1 or "blah", it doesn't matter. – Anand Mar 28 '17 at 20:03
1

I see now, so its a matter of efficiency (why select all the columns when you don't need to kind of thing), thanks! – Joseph Astrahan Mar 28 '17 at 20:42

How to group by in SQL by largest date (Order By a Group By)

1 Answers1

Linked