如何按SQL按最大日期分组（按分组顺序排列） [英] How to group by in SQL by largest date (Order By a Group By)

问题描述

我有以下数据库表

这是我的样本数据。

我试图弄清楚如何做的是如何编写一个查询来选择所有apntoken，通过deviceid选择userid = '20'分组，然后通过apntoken（除了它应该显示最近的apntoken）。

我试过的一些查询是这样的。

  SELECT 
 DISTINCT apntoken，deviceid，创建
从`distribution_mobiletokens` 
 WHERE userid = '20'
 GROUP BY deviceid 
  
 
 
 
这将返回以下结果。 
 
 
  
 
 
 
注意日期不是最新的日期。我添加了一个名为'latestone'的虚假条目，用于将日期设置到未来。
 
 
 
我试过的另一个查询是这样的。
  SELECT 
 DISTINCT apntoken，deviceid，创建
 FROM`distribution_mobiletokens` 
 WHERE userid = '20'
 GROUP BY deviceid，apntoken 
  
然而，这个问题是我现在有了deviceid的额外数据，因为它没有独一无二？
 
 
 
参考图片
 
 
 
   p> 
 
 
我不知道如何修改这个查询来做到这一点，或者如果这很容易吗？
 
 
 
例如我应该拿回我只有'2'字段的数据。一个用于deviceid 5628CF60-D0CF-479A-A944-A3496E085FC8& 948D9BAD-B164-4830-ACEB-08A089A80558喜欢在图片中查看我试过的第一个查询，但它应该显示说'lastone'的apntoken 
 
 
 
我需要这个的原因因此我总是将最新令牌发送给苹果，以便为特定设备发送推送通知。每次用户登录该设备都是一样的，但这可能是不同的。所以我想，如果我可以从设备中为用户带来最新的戒备，那么这将成功。
 
 
 
我尝试过的其他查询
  SELECT apntoken，deviceid，创建
从`distribution_mobiletokens` 
 WHERE userid = '20'
 GROUP BY deviceid ORDER BY创建了DESC 
  
  
 
 
 
请注意，它不会显示latestone应显示其日期 
 
解决方案
我最喜欢的方式构造这个SQL是使用不存在子句，如下所示： 
 
 
  SELECT apntoken，deviceid，创建
从`distribution_mobiletokens`作为dm 
 WHERE userid = '20'
并且不存在（
从`distribution_mobiletokens`中选择1 
 
 where userid ='20'
 and d eviceid = dm.deviceid 
并创建> dm.created 
）
  
 
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
解决方案
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
    )


                        
这篇关于如何按SQL按最大日期分组（按分组顺序排列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！