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

User · Accepted Answer

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

User · Answer

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

User · Answer

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 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  lt  t2 timestamp where t2 timestamp is null order by t1 timestamp desc

User · Answer

Why make it so complicated   This worked   SELECT m id v id MAX TIMESTAMP  AS TIME  FROM table name   GROUP BY m id

User · Answer

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

User · Answer

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

User · Answer

You can try this   SELECT tbl   FROM  SELECT   FROM table ORDER BY timestamp DESC  as tbl  GROUP BY tbl m id

[mysql] Using ORDER BY and GROUP BY together

Examples related to mysql

Examples related to sql

Examples related to group-by

Examples related to greatest-n-per-group