15,302,965 members
See more:
I have 3 tables : Author, Book and Sales with columns as below. I need to find the highest and lowest selling books (that had at least one sale) by quantity.

Table 1 : Author

| id | Author | Birth_year |
|----------------------------
| 1 | Smith | 2000
| 2 | Jack | 2001
| 3 | Carol | 2002
| 4 | Dave | 2003
Table 2 : Book

| id | Title | Published_year | Author_id |
|---------------------------------------------------------
| 1 | wall street | 1995 | 4
| 2 | wealth management | 1990 | 2
| 3 |Smart Tips for Success | 1985 | 1
| 4 | Keep Moving | 1980 | 3
| 5 | Fly or Die | 1975 | 1
Table 3 : Sales

| id | Sales_dt | book_id | Sales_price |
|---------------------------------------------------------
| 1 | 12-jan-2020 | 1 | 15
| 2 | 12-jan-2020 | 2 | 9
| 3 | 13-jan-2020 | 1 | 15
| 4 | 14-jan-2020 | 1 | 15
| 5 | 15-jan-2020 | 4 | 12

What I have tried:

Given these 3 tables without quantity column. How to order the query by quantity? Also how to check revenue as the question also states: If there are multiple possible books with the same quantity or revenue, sort by author ID and then by publishing year. Please help
Posted
Updated 8-Feb-21 19:58pm

## Solution 1

Start with ORDER BY: SQL ORDER BY Keyword[^] and JOIN: SQL Joins[^]
It's pretty easy when you get the hang of it:
SQL
```SELECT c.CustomerName, o.OrderQuantity
FROM Customers c
JOIN Orders o
ON o.CustomerID = c.ID
ORDER BY o.OrderValue DESC```