Reputation: 463
Consider the relation Book with the following simple schema
Book(title, pages)
I want to find the title(s) of the book(s) with the highest number of pages, but without using a subquery.
Is this possible?
EDIT: This is not for a particular DBMS. I'm studying from a book (which uses the SQL-99 Standard) and this is a part from an exercise.
Upvotes: 1
Views: 569
Reputation: 4757
With an except
. You find all the books that have another one with more pages (lower part) and then you search all the books except those ones.
select title,pages
from books
Except --minus if Oracle
select b1.title,b1.pages
from books b1 join books b2
on b1.pages < b2.pages
Upvotes: 4
Reputation:
This is typically solved using a window function
select title, pages
from (
select title, pages,
dense_rank() over (partition by title order by pages desc) as rnk
from books
) t
where rnk = 1;
However the above is SQL:2003 not SQL99. You can also re-write this to use a common table expression:
with ranked as (
select title, pages,
dense_rank() over (partition by title order by pages desc) as rnk
from books
)
select *
from ranked
where rnk = 1;
If you consider derived tables or common table expressions a "sub-query" then (in standard SQL) you could also limit the number of rows:
select title, pages
from books
order by pages desc
fetch first 1 rows only;
If two books have the same number of maximum pages, then this will however only show one of them, whereas the other two solutions will show both.
Upvotes: 0
Reputation: 45096
select top (1) with ties title, pages
from books
order by pages desc
Upvotes: 0