sql: trying to select the second biggest element but selects the biggest

Question

we want to have the second biggest element. We first use ANY to exclude the biggest one. Then we use all to select the biggest. However when we run this query, it shows the biggest and not the second one. Why?

SELECT * 
FROM bestelling
WHERE totaalprijs > ALL
(
    SELECT totaalprijs
    FROM bestelling
    WHERE totaalprijs < ANY
    (
        SELECT totaalprijs
        FROM bestelling

    )
)

elements in the table:

157.00
5.00
82.80
15.00
20.00
20.00

Answer 1

How about

select * 
from bestselling 
where totaalprijs < (select max(totaalprijs) from bestselling)
order by totaalprijs desc
limit 1;

Answer 2

This is also a good solution that is more obvious that you are selecting the first record that meets the criteria, if you are using MSSQL.

select Top 1 
from bestselling 
where totaalprijs < (select max (totaalprijs) from bestselling)
order by totaalprijs desc

Using

select * 
from bestselling 
where totaalprijs < (select max (totaalprijs) from bestselling)
order by totaalprijs desc
limit 1;

will work for mySQL.

Answer 3

OK, then for Oracle how about:

select * 
from bestselling 
where totaalprijs < (select max(totaalprijs) from bestselling)
and rownum < 2
order by totaalprijs desc;

Answer 4

The problem is the ">" in your outermost query. If you break this down in language, from the inside out, you're saying:

• From all the rows in the table ...
• Get me any where totaalprijs is less than some other value (i.e. all records except records with the max value in the table, 157.00) ...
• Then, get me any values that are bigger than all the records in the previous table. Which is, naturally, just the biggest one.

If you just want the second biggest value, you can replace that outer clause with a MAX statement:

SELECT max(totaalprijs)
FROM 
(
    SELECT totaalprijs
    FROM bestelling
    WHERE totaalprijs < ANY
    (
        SELECT totaalprijs
        FROM bestelling
    )
) M

Or, if you want all records that bear that "second biggest" value, you can use:

SELECT * FROM bestelling 
WHERE totaalprijs = 
(
    SELECT max(totaalprijs)
    FROM bestelling
    WHERE totaalprijs < ANY
    (
        SELECT totaalprijs
        FROM bestelling
    )
)

Answer 5

Innermost query: list all values

Middle query: list all values where for each there exists at least one value greater than it

Outer query: list all values where those values are greater than any from the middle query

So the middle query gets everything except the largest, and the outer query lists everything larger than everything except the largest value -- which will be the largest value.

The following adjusted query should do it, though you'll have to figure what to do if there are two or more rows that both contain the second-largest value.

SELECT *  
FROM bestelling 
WHERE totaalprijs =
 ( 
    SELECT max(totaalprijs)
    FROM bestelling 
    WHERE totaalprijs < ANY 
    ( 
        SELECT totaalprijs 
        FROM bestelling 

    ) 
) 

Answer 6

This solution uses just ALL and ANY to get the result.

This part excludes the largest value.

SELECT totaalprijs
FROM bestelling
WHERE totaalprijs < ANY
(
    SELECT totaalprijs
    FROM bestelling

)

So, now we have a result set where the largest value is actually the second largest. Now, we must exclude all the smaller elements from the result set. Therefore, we must select from the first result set, not from bestelling again.

SELECT * 
FROM
(
    SELECT totaalprijs
    FROM bestelling
    WHERE totaalprijs < ANY
    (
        SELECT totaalprijs
        FROM bestelling

    )
)
WHERE totaalprijs >= ALL
(
    SELECT totaalprijs
    FROM bestelling
    WHERE totaalprijs < ANY
    (
        SELECT totaalprijs
        FROM bestelling
    )
 )