w3resource

SQL: Find the details of 1970 winners by the ordered subject and winner name

SQL Basic Select Statement: Exercise-24 with Solution.

From the following table, write a SQL query to find the details of 1970 Nobel Prize winners. Order the results by subject, ascending except for 'Chemistry' and ‘Economics’ which will come at the end of the result set. Return year, subject, winner, country, and category.

Sample table: nobel_win

 

Sample Solution :

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

Output of the Query:

year	subject		winner				country		category
1970	Literature	Aleksandr Solzhenitsyn		Russia		Linguist
1970	Physics		Hannes Alfven			Sweden		Scientist
1970	Physics		Louis Neel			France		Scientist
1970	Physiology	Bernard Katz			Germany		Scientist
1970	Physiology	Julius Axelrod			USA		Scientist
1970	Physiology	Ulf von Euler			Sweden		Scientist
1970	Chemistry	Luis Federico Leloir		France		Scientist
1970	Economics	Paul Samuelson			USA		Economist

Practice Online


Query Visualization:

Duration:

Query visualization of 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 - Duration

Rows:

Query visualization of 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 - Rows

Cost:

Query visualization of 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 - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find the Nobel Prize winners for the subject not started with the letter 'P'. Return year, subject, winner, country, and category. Order the result by year, descending.
Next: From the following table, write a SQL query to select a range of products whose price is in the range Rs.200 to Rs.600. Begin and end values are included. Return pro_id, pro_name, pro_price, and pro_com.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

Nested select statement in SQL Server.

You need to alias the subquery.

SELECT name FROM (SELECT name FROM agentinformation) a 

or to be more explicit

SELECT a.name FROM (SELECT name FROM agentinformation) a  

Database: SQL Server

Ref: https://bit.ly/3Qruiu5