SQL Exercise: Oldest orders first on the highest purchase amount
Orders Sorted by Date & Amount Descending
From the following table, write a SQL query to find all the orders. Sort the result-set in descending order by ord_date and purch_amt. Return all fields.
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution:
-- This query selects all columns from the 'orders' table and orders the result set first by 'ord_date' in ascending order,
-- and then by 'purch_amt' in descending order.
SELECT *
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Orders the result set first by 'ord_date' column in ascending order,
-- and then by 'purch_amt' column in descending order.
ORDER BY ord_date, purch_amt DESC;
Output of the Query:
ord_no purch_amt ord_date customer_id salesman_id 70013 3045.60 2012-04-25 3002 5001 70012 250.45 2012-06-27 3008 5002 70005 2400.60 2012-07-27 3007 5001 70004 110.50 2012-08-17 3009 5003 70011 75.29 2012-08-17 3003 5007 70008 5760.00 2012-09-10 3002 5001 70007 948.50 2012-09-10 3005 5002 70009 270.65 2012-09-10 3001 5005 70001 150.50 2012-10-05 3005 5002 70002 65.26 2012-10-05 3002 5001 70003 2480.40 2012-10-10 3009 5003 70010 1983.43 2012-10-10 3004 5006
Code Explanation:
The said query in SQL retrieves all columns from the 'orders' table and returns the result sorted first by the "ord_date" column in ascending order, and then by the "purch_amt" column in descending order. The "ORDER BY ord_date, purch_amt DESC" clause sorts the rows in the result set first by ord_date values, and then for the same ord_date values, it sorts them by the purch_amt column in descending order.
Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

Visual presentation:

Go to:
PREV : Orders Sorted by Date Descending.
NEXT : Customers Sorted by ID.
Practice Online
Query Visualization:
Duration:

Rows:

Cost:

For more Practice: Solve these Related Problems:
- Write a SQL query to retrieve all orders sorted by ord_date descending and then by purch_amt descending, excluding orders with a NULL ord_date.
- Write a SQL query to list orders sorted in descending order by both ord_date and purch_amt, including only orders where purch_amt is above the median value.
- Write a SQL query to select all order fields and sort by ord_date and purch_amt descending, with a condition that ord_no is divisible by 3.
- Write a SQL query to output orders sorted by ord_date descending, then by purch_amt descending, and compute the rank of each order within its order date.
- Write a SQL query to display all orders sorted by ord_date and purch_amt in descending order, along with a column calculating the difference between purch_amt and the average purchase amount for that date.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.