Using ORDER BY and GROUP BY together

0 votes

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

Can someone please help me with this?

Sep 19, 2022 in Database by Kithuzzz
• 38,010 points
500 views

1 answer to this question.

0 votes

Correctly use the  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
answered Sep 20, 2022 by narikkadan
• 63,420 points

Related Questions In Database

0 votes
1 answer

What is the rule to use group by, having and where clause?

Hi samar, this is a very common mistake ...READ MORE

answered Jul 2, 2019 in Database by anonymous
1,062 views
0 votes
1 answer

SQL Group By with an Order By

In any version of MySQL, you can use the aggregate ...READ MORE

answered Feb 10, 2022 in Database by Vaani
• 7,020 points
244 views
0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,010 points
406 views
0 votes
0 answers

How to group by month from Date field using sql

How can I only group by month ...READ MORE

Aug 20, 2022 in Database by Kithuzzz
• 38,010 points
747 views
0 votes
1 answer
0 votes
1 answer

What is GROUP BY statement in MySQL?

This statement is used with the aggregate ...READ MORE

answered Nov 23, 2018 in Database by Sahiti
• 6,370 points
490 views
0 votes
1 answer

How to change the default value and to alter a column in sql?

Hi, You can try this: ALTER TABLE foobar_data CHANGE ...READ MORE

answered Jun 24, 2019 in Big Data Hadoop by Gitika
• 65,910 points
1,188 views
0 votes
1 answer

What is an index in SQL?

An index is used to speed up ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,020 points
408 views
0 votes
1 answer

How can I use LTRIM/RTRIM to search and replace leading/trailing spaces?

Use LTRIM/RTRIM to eliminate spaces from the ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,420 points
802 views
0 votes
1 answer

Difference between EXISTS and IN in SQL?

Although the exists keyword can be used ...READ MORE

answered Sep 11, 2022 in Database by narikkadan
• 63,420 points
531 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP