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 in Database by Kithuzzz
• 12,240 points
15 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 by narikkadan
• 20,880 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
648 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 in Database by Vaani
• 7,020 points
33 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 in Database by Kithuzzz
• 12,240 points
27 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 in Database by Kithuzzz
• 12,240 points
73 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,380 points
312 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,890 points
456 views
0 votes
1 answer

What is an index in SQL?

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

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

What's the difference between VARCHAR and CHAR?

Any Unicode data can be stored in ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
18 views
0 votes
1 answer

Using RegEx in SQL Server

Because LIKE can be used instead of managed ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
22 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