CODEWITHSUNDEEP
mysqlsql
rohan
rohan

Reputation: 557

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.

Upvotes: 0

Views: 223

Answers (2)

su_sundariya
su_sundariya

Reputation: 73

you can try this also 

 select id,field1,field2,field3
 from tabel1 
 group by field3
 order by field1 DECS and field2 DECS

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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.

Upvotes: 2

Related Questions