一起使用ORDER BY和GROUP BY [英] Using ORDER BY and GROUP BY together

问题描述

我的表看起来像这样（我使用的是MySQL）：

  m_id | v_id |时间戳
 ------------------------ 
 6 | 1 | 1333635317 
 34 | 1 | 1333635323 
 34 | 1 | 1333635336 
 6 | 1 | 1333635343 
 6 | 1 | 1333635349 
  

我的目标是取每个m_id一次，并按最高时间戳排序。

结果应该是：

  m_id | v_id |时间戳
 ------------------------ 
 6 | 1 | 1333635343 
 34 | 1 | 1333635336 
  

我写了这个查询：

 SELECT * FROM表GROUP BY m_id ORDER BY timestamp DESC 
  

但是，结果是：

  m_id | v_id |时间戳
 ------------------------ 
 34 | 1 | 1333635323 
 6 | 1 | 1333635317 
  

我认为这是因为它首先执行GR​​OUP_BY，然后对结果进行排序。 b
$ b

任何想法？谢谢。

解决方案

正确使用 group by ：

  select l。* 
 from table l 
 inner join（
 select 
 m_id，max（timestamp）最近
来自表
组by m_id 
）r 
对l.timestamp = r.latest和l.m_id = r.m_id 
按时间戳顺序登录
  

这是如何工作的：

• 为子查询中的每个不同的 m_id 选择最新的时间戳


• 从 table 中的行匹配子查询中的一行（此操作 - 执行连接，但没有从第二个表中选择列，它仅用作过滤器 - 被称为semijoin，以防您好奇）


• 对行进行排序

My table looks like this (and I'm using MySQL):

m_id | v_id | timestamp
------------------------
6    |   1  | 1333635317
34   |   1  | 1333635323
34   |   1  | 1333635336
6    |   1  | 1333635343
6    |   1  | 1333635349

My target is to take each m_id one time, and order by the highest timestamp.

The result should be:

m_id | v_id | timestamp
------------------------
6    |   1  | 1333635343
34   |   1  | 1333635336

And i wrote this query:

SELECT * FROM table GROUP BY m_id ORDER BY timestamp DESC

But, the results are:

m_id | v_id | timestamp
------------------------
34   |   1  | 1333635323
6    |   1  | 1333635317

I think it causes because it first does GROUP_BY and then ORDER the results.

Any ideas? Thank you.

解决方案

One way to do this that correctly uses group by:

select l.* 
from table l
inner join (
  select 
    m_id, max(timestamp) as latest 
  from table 
  group by m_id
) r
  on l.timestamp = r.latest and l.m_id = r.m_id
order by timestamp desc

How this works:

• selects the latest timestamp for each distinct m_id in the subquery
• only selects rows from table that match a row from the subquery (this operation -- where a join is performed, but no columns are selected from the second table, it's just used as a filter -- is known as a "semijoin" in case you were curious)
• orders the rows

这篇关于一起使用ORDER BY和GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！