dshri dshri - 5 months ago 27
SQL Question

how to list mysql query result in particular sequence?

the problem #14 from SQLZoo
for table
nobel(yr, subject, winner)
goes as
"Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last."

my solution is

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject,winner


but it does not list chemistry and physics at last.
is there a way to do so?
and how to order a field in particular sequence?

Answer

Use can useCASE:

SELECT winner, subject
FROM nobel
WHERE yr=1984 
ORDER BY
 CASE
    WHEN subject IN ('Physics','Chemistry') THEN 1
    ELSE 0
 END ASC,
 subject,
 winner

EDIT:

This is primary the same as Gordon Linoff Solution, but can be extended to another subjects if needed using more rules. IN allows only 2 ways: false/true.

ORDER BY
 CASE
    WHEN subject IN ('Physics','Chemistry') THEN 2
    WHEN subject IN ('Medicine','Literature') THEN 1
    ELSE 0
 END ASC,
 subject,
 winner