Search code examples
mysqlsql-order-by

MySQL: Ordering

I have a table with the names and birth dates of kids in a class. I want to create a query which sorts the kids first by whether they were born before 2010 or after and then sorts them by name.

The outcome I'm looking for would be:

Name            DoB
Adam Brown      2009-12-04
Charles White   2009-11-07
David Green     2009-11-11
Andrew Black    2010-04-07
Brian Jones     2010-02-22
Jack Smith      2010-01-15

The query I could come up for now is a simple:

SELECT name, dob FROM class_tbl ORDER BY name ASC

I don't know how to work in the DoB ordering.
Is it possible to do so in one single query?

Solution

  • You can do:

    select * from t order by case when dob < '2010-01-01' then 0 else 1 end, name;

    Result:

     name           dob        
 -------------- ---------- 
 Adam_Brown     2009-12-04 
 Charles_White  2009-11-07 
 David_Green    2009-11-11 
 Andrew_Black   2010-04-07 
 Brian_Jones    2010-02-22 
 Jack_Smith     2010-01-15

    See running example at db<>fiddle.