Da Mike Da Mike - 1 year ago 51
SQL Question

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.

Answer Source

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