I am trying to select the highest pair from a table that has been created by joining (combining) two tables. I guess I should use a nested query but I'm not sure how. I also came around a similar question that seems a bit less complex, but I am struggling to implement it into my problem.
Similar question: How to select max timestamp from each currency pair SQL?
My tables:
Book:
| title | publisher | price | sold |
|---|---|---|---|
| book1 | A | 5 | 300 |
| book2 | B | 15 | 150 |
| book3 | A | 8 | 350 |
Publisher:
| code | name |
|---|---|
| A | ABook |
| B | BBook |
| C | CBook |
My query:
SELECT b.titel, p.name, max(b.price*b.sold) as 'Revenue"
FROM publisher p, book b
WHERE p.code = b.publisher
Gives:
| title | publisher | Revenue |
|---|---|---|
| book1 | ABook | 1500 |
| book2 | BBook | 2250 |
| book3 | ABook | 2800 |
Desired output:
| title | publisher |
|---|---|
| book2 | BBook |
| book3 | ABook |
How to alter my query to get the highest revenue per book title and the corresponding publisher?
CodePudding user response：
You can use this query:
SELECT b.titel, p.name
FROM publisher p, book b
WHERE p.code = b.publisher
order by b.price*b.sold desc
OR
select abc.titel,abc.name FROM (
select b.titel, p.name, max( b.price*b.sold) as balance
FROM publisher p, book b
WHERE p.code = b.publisher
group by b.titel, p.name ) abc order by abc.balance desc
CodePudding user response：
You can use row_number window function to select the appropriate row for each group.
Your desired results don't align with your description (do you want a revenue column or not?), however this produces your desired output. Note the use of modern (for 30 years) ansi join syntax:
with sold as (
select *, Row_Number() over(partition by publisher order by (price * sold) desc) rn
from book b join publisher p on p.code=b.publisher
)
select title, name Publusher
from sold
where rn=1