rohan rohan - 2 years ago 114
MySQL Question

MYSQL - Group by and Order By

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.

Answer Source

Try this:

SELECT t1.ID, t1.Field1, t1.Field2, t1.Field3
FROM mytable AS t1
  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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download