I don't know how to order the names after I sorted out the subject physics and chemistry!
Question: The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
SELECT winner, subject, subject IN('Physics', 'Chemistry')
FROM nobel
WHERE yr=1984
ORDER BY CASE
WHEN subject IN ('Physics', 'Chemistry') = 0 THEN subject IN ('Physics', 'Chemistry')
WHEN subject IN ('Physics', 'Chemistry') = THEN winner
ELSE winner
End
url to problem for more details http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial
I have tried in SQLZOO and following SQL gives correct result.
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'), subject, winner
The in
operator can't be used in the select as you did, but you were on the right path using a case expression in the order by
clause. What you want is this:
SELECT
winner, subject
FROM
nobel
WHERE
yr = 1984
ORDER BY
CASE WHEN subject IN ('Physics','Chemistry') THEN 1 ELSE 0 END,
subject,
winner
As the problem states IN ('Physics','Chemistry')
will evaluate to 1 or 0 so it can be used instead on the full case expression in some databases; though it's not conformant with standard ANSI SQL. MySQL will allow it I believe, but for example MS SQL will not.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With