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?
You can add additional keys into the order by. For this particular purpose:
order by (subject in ('Chemistry', 'Physics') ),
subject, winner
The first condition is a boolean expression that evaluates to either 1 (for true) or 0 (for false). Hence, the falses appear before the trues.
order by does.SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Chemistry', 'Physics'), subject, winner
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
select subject, winner,yr
from nobel
where yr=1984
order by subject in ('Chemistry','Physics'),subject,winner
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY
case when subject IN ('Physics','Chemistry') then 0
ELSE 1
END desc,subject,winner
