CODEWITHSUNDEEP
mysqlsql
micha
micha

Reputation: 69

Complex query in MySQL

Here is my Problem: I have a table that holds a few columns

(Id, email, last_visit_date, picture)

The picture column can has 3 options:

I need a query (for MySQL) that will return all rows in the order:

  1. All the users with pictures order by last_visit_date
  2. All the users with private pictures order by last_visit_date
  3. All the users without pictures order by last_visit_date

I could not figure that out

Upvotes: 1

Views: 82

Answers (3)

DRapp
DRapp

Reputation: 48179

Use a case/when construct in your order by clause... In this case, I set values to 1, 2 or 3, THEN order by the last visit date...

select
      YT.*
   from
      YourTable YT
   order by
      case when YT.Picture = "noPic" then 1
           when YT.Picture = "PrivatePic" then 2
           else 3 
      end,
      YT.Last_Visit_Date desc

Upvotes: 0

knittl
knittl

Reputation: 265918

mysql will implicitly convert bool values to integers (1 and 0 respectively). you can thus sort by comparing the picture value:

ORDER BY picture = 'privatePic' ASC, picture = 'noPic' ASC, last_visit_date

Upvotes: 0

Karoly Horvath
Karoly Horvath

Reputation: 96326

ORDER BY picture LIKE 'pic%' DESC, picture = "noPic" DESC, last_visit_date

Upvotes: 1

Related Questions