case and order by - How to specify that something should be shown in the end of the list?

user_462608 0
Viewed 56

From: https://www.w3resource.com/sql-exercises/sql-retrieve-exercise-24.php

Write a SQL query to find all the details of 1970 winners by the ordered to subject and winner name; but the list contain the subject Economics and Chemistry at last.

SELECT *
FROM nobel_win
WHERE year=1970 
ORDER BY
 CASE
    WHEN subject IN ('Economics','Chemistry') THEN 1
    ELSE 0
 END ASC,
 subject,
 winner;

The CASE here seems to return true if Economics and Chemistry are in subject column. Which part specifies here that Economics and Chemistry should be shown in the "end"?

mysql sql database
1 Answers

The case expression included here will evaluate to 1 when the Subject is 'Economics' or 'Chemistry'. Otherwise it will return a 0.

This is included in the sql query as the first sorting term, to be sorted in Ascending order. For the two values of 0 and 1 that are possible, this will sort them in that order: 0 first, then 1.

In other words, the very first sort will be:

  1. All rows where the subject is not Economics or Chemistry
  2. All rows where the subject is Economics or Chemistry

Following this, it sorts by subject and winner (so as a side effect, in the end of the list, Chemistry will always sort before Economics).

user_127246740
Related