CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
MrUpsidown
MrUpsidown

Reputation: 22487

MySQL sorting group-by results

I have the following tables structure:

table_1

id     title
------------
155    item1
156    item2
157    item3

table_2

id     model
------------
155    100
156    100
157    200

table_3

id     cid
------------
155    5
156    5
157    5

I want to group my results by model (from table_2) and make sure it returns the highest id (order by id descending).

I have tried the following, but the order by clause doesn't seem to work:

SELECT a.id, b.model FROM table_1 as a
INNER JOIN table_2 as b on b.id = a.id
INNER JOIN table_3 as c on c.id = a.id
WHERE c.cid = 5
GROUP BY b.model
ORDER BY a.id desc

What am I doing wrong?

Upvotes: 0

Views: 56

Answers (2)

pala_
pala_

Reputation: 9010

SELECT max(a.id), b.model FROM table_1 as a
INNER JOIN table_2 as b on b.id = a.id
INNER JOIN table_3 as c on c.id = a.id
WHERE c.cid = 5
GROUP BY b.model
ORDER BY max(a.id) desc

Demo here: http://sqlfiddle.com/#!9/afb0b/2

The reason this works while your initial attempt did not, is that any field not present in the GROUP BY clause, or not used in an aggregate function (such as MAX), will have an indeterminate value (ie, the mysql engine makes no guarantees as to which value from the group you will get).

MySQL GROUP BY Handling

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

In fact, MySQL is one of the few (only?) sql databases that will let you put a non aggregated, non grouped field in the select clause without erroring.

Upvotes: 4

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can do as

select 
t1.id, 
t2.model 
from table_1 t1 join table_3 t3 on t3.id = t1.id 
join ( 
  select max(id) as id , model from table_2 group by model 
)t2 on t1.id=t2.id 
where t3.cid = 5;

Upvotes: 1

Related Questions