MySQL ORDER BY then GROUP By

Question

I have the following:

SELECT * FROM (SELECT * FROM table ORDER BY id DESC) t GROUP BY pcid

SELECT * FROM (SELECT * FROM table ORDER BY timestamp DESC) t GROUP BY pcid

I am unable to get the GROUP BY to be in the correct order. It always pulls from the lowest value.

Am I missing some syntax that is oblivious to me?

After looking for the past several hours to find a solution to this issue, I have been unable to resolve. This question has been asked because I have been unable to find the answer.

Here is my table structure:

table id,primary pcid cpu memphy memcom memvir timestamp

I have tried sorting by timestamp and id with no luck.

why are you using a nested query to do order by and group by? Can you show us table structure and sample data? — Lelio Faieta, May 25 '16 at 14:42
Are you trying to get the row with the highest value for each value of `column`? Then see http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 — Barmar, May 25 '16 at 14:43
Possible duplicate of [MySQL Group By ordering](http://stackoverflow.com/questions/924494/mysql-group-by-ordering) — msanford, May 25 '16 at 14:47
So why would I use a join when all the information is in one table — Joseph Monarch, May 25 '16 at 15:55

score 0 · Answer 1 · edited May 25 '16 at 14:52

0

You should give more detail but it might be something like this :

 SELECT column, highest, count(somecolumnname)
 FROM table_name 
 GROUP BY column, highest 
 ORDER BY highest DESC

edited May 25 '16 at 14:52

Hamza Zafeer

2,360
13
30
42

answered May 25 '16 at 14:46

Ufuk Ugur

186
1
17

score 0 · Answer 2 · answered May 25 '16 at 14:50

This query should return the rows that you need:

select
  t.*
from
  table t INNER JOIN (select column, max(highest) m_highest
                      from table
                      grouo by column) tm
  on t.column=tm.column and t.highest=tm.m_highest

The subquery will return the maximum highest per column, then you join back to the original table t and return all rows that have highest=the maximum highest per column.

MySQL ORDER BY then GROUP By

2 Answers2