CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
dreadnought303
dreadnought303

Reputation: 157

Added condition to ORDER BY statement

I found this question on a website and was trying to solve it to no avail. There is a table of Nobel prize winners with columns name, year and subject. The question is this: Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

I came up with the following:

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

which gives the result:

winner               subject
Richard Stone        Economics
Jaroslav Seifert     Literature
Niels K. Jerne       Medicine
Georges J.F. Köhler Medicine
César Milstein      Medicine
Desmond Tutu         Peace
Bruce Merrifield     Chemistry
Carlo Rubbia         Physics
Simon van der Meer   Physics

I however, require that the order of winners for each subject be further sorted by name i.e. the result above should become Caesar Milstein as 3rd, followed by Georges J.K. Kahler and then Niels K. Jerne.

Upvotes: 2

Views: 297

Answers (5)

dreadnought303
dreadnought303

Reputation: 157

Thanks everyone for the help. However, as I mentioned in one of the comments, the specific website I tested these queries on didn't give me an output for the answers posted here. What worked though is posted by HABO in this comment.

Upvotes: 1

slnit
slnit

Reputation: 35

I think this works 

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

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

As everyone has mentioned, you just need ,winner added. But you can simplify this a bit too:

ORDER BY CASE WHEN subject NOT IN ('Chemistry','Physics') THEN 1 ELSE 2 END, winner

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146449

Order By case When Subject in ('Chemistry', 'Physics') then 1 else 0 end, winner, Subject

Upvotes: 1

Mat Richardson
Mat Richardson

Reputation: 3606

Just add the winner to your order by statement:-

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

Upvotes: 2

Related Questions