Da Mike
Da Mike

Reputation: 463

SQL query - Finding the max value of an attribute without a subquery

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

Answers (3)

vercelli
vercelli

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

user330315
user330315

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

paparazzo
paparazzo

Reputation: 45096

select top (1) with ties title, pages
from books  
order by pages desc

select top

Upvotes: 0

Related Questions