MYSQL - Group by and Order By

Question

I have a table as such:

ID    Field1    Field2    Field3
1     Apple     Fruit     Cheap
2     Apple     Fruit     Eatable
3     Apple     Food      Something
4     Banana    Fruit     Cheap
5     Banana    Food      Eatable
6     Cat       Pet       Weird
7     Cat       Pet       Friend
8     Cat       Pet       Amazing
9     Cat       Animal    Cheap

I want to get distinct Field1, Field 2 elements and sort by Field3 containing "Cheap". I'm expecting this:

ID    Field1    Field2    Field3
1     Apple     Fruit     Cheap
4     Banana    Fruit     Cheap
9     Cat       Animal    Cheap
3     Apple     Food      Something    
5     Banana    Food      Eatable
6     Cat       Pet       Weird

Result has row with ID=2,7,8 are removed as ID=2 has same field1, field2 as ID=1 and ID=7,8 have same field1, field2 as ID=6. ID = 1 and ID = 2 have same Field1 and Field2. Only one of the ID = 6,7,8 get into result for having distinct Field1, Field2.

I have tried "grouping" and "order by field()", but for having "grouping" desired rows are getting eliminated. For example, after grouping ID = 1 is not present and ID = 2 row is present.

My current query is:

select * from tbl
group by field1,field2
order by field(field3,"CHEAP") desc;

Which give me only two rows with field3="Cheap" as "group by" is considering ID=2 instead of ID=1

Can anyone assist me with this?

Thanks in advance.

I want to make sure all the rows with field3 = "Cheap" are there in the result. I think I should be using some sort of subquery instead of group by. — rohan
Why do you want to group the first and second field when you want to display data with record of Cheap in third field? — rhavendc
Just trying to get distinct of first and second fields and sort by third field. I'm not sure about the right approach. — rohan

Giorgos Betsos Giorgos Betsos · Accepted Answer · 2016-05-27T08:12:54

Try this:

SELECT t1.ID, t1.Field1, t1.Field2, t1.Field3
FROM mytable AS t1
JOIN (
  SELECT Field1, Field2, MAX(Field3) AS min_field3,
         COUNT(CASE WHEN Field3 = 'Cheap' THEN 1 END) AS cnt_cheap
  FROM mytable
  GROUP BY Field1, Field2
) AS t2 ON t1.Field1 = t2.Field1 AND 
           t1.Field2 = t2.Field2 AND
           t1.Field3 = IF(t2.cnt_cheap = 1, 'Cheap', min_field3)
ORDER BY FIELD(Field3, 'Cheap') DESC

The above query picks the 'Cheap' record from a Field1, Field2 group, if such a record exists, otherwise it picks the record having the maximum Field3 value.

MYSQL - Group by and Order By

2 Answers