Using ORDER BY and GROUP BY together

Question

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  | 1333635349
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.

Answer 1

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

Answer 2

If you really don't care about which timestamp you'll get and your v_id is always the same for a given m_i you can do the following:

select m_id, v_id, max(timestamp) from table
group by m_id, v_id
order by max(timestamp) desc

Now, if the v_id changes for a given m_id then you should do the following

select t1.* from table t1
left join table t2 on t1.m_id = t2.m_id and t1.timestamp < t2.timestamp
where t2.timestamp is null
order by t1.timestamp desc

Answer 3

Here is the simplest solution

select m_id,v_id,max(timestamp) from table group by m_id;

Group by m_id but get max of timestamp for each m_id.

Answer 4

You can try this

 SELECT tbl.* FROM (SELECT * FROM table ORDER BY timestamp DESC) as tbl
 GROUP BY tbl.m_id  

Answer 5

SQL>

SELECT interview.qtrcode QTR, interview.companyname "Company Name", interview.division Division 
FROM interview 
JOIN jobsdev.employer 
    ON (interview.companyname = employer.companyname AND employer.zipcode like '100%')
GROUP BY interview.qtrcode, interview.companyname, interview.division
ORDER BY interview.qtrcode;

Answer 6

I felt confused when I tried to understand the question and answers at first. I spent some time reading and I would like to make a summary.

1. The OP's example is a little bit misleading. At first I didn't understand why the accepted answer is the accepted answer.. I thought that the OP's request could be simply fulfilled with

select m_id, v_id, max(timestamp) as max_time from table
group by m_id, v_id
order by max_time desc

Then I took a second look at the accepted answer. And I found that actually the OP wants to express that, for a sample table like:

m_id | v_id | timestamp
------------------------
6    |   1  | 11
34   |   2  | 12
34   |   3  | 13
6    |   4  | 14
6    |   5  | 15

he wants to select all columns based only on (group by)m_id and (order by)timestamp.

Then the above sql won't work. If you still don't get it, imagine you have more columns than m_id | v_id | timestamp, e.g m_id | v_id | timestamp| columnA | columnB |column C| .... With group by, you can only select those "group by" columns and aggreate functions in the result.

By far, you should have understood the accepted answer. What's more, check row_number function introduced in MySQL 8.0:

https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/

2. Finding top N rows of every group

It does the simlar thing as the accepted answer.

2. Some answers are wrong. My MySQL gives me error.

select m_id,v_id,max(timestamp) from table group by m_id;

@abinash sahoo

SELECT m_id,v_id,MAX(TIMESTAMP) AS TIME
 FROM table_name 
 GROUP BY m_id

@Vikas Garhwal

Error message:

[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb.test_table.v_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 7

Just you need to desc with asc. Write the query like below. It will return the values in ascending order.

SELECT * FROM table GROUP BY m_id ORDER BY m_id asc;

Answer 8

Why make it so complicated? This worked.

SELECT m_id,v_id,MAX(TIMESTAMP) AS TIME
 FROM table_name 
 GROUP BY m_id