SQL: getting rows where some 'x' column value is a maximal one [duplicate]

Question

I am trying to get data from my database. Query upon sub-query upon another sub-query - and as the intermediate result I get looks like this:

item	quantity
pen	34
pencil	42
notebook	42
eraser	12

I need to build another query upon this result set to get the rows where item_quantity has it's maximal value (42 in the example above). The rows with pencils and notebooks. However, I have found out that the task is a bit trickier than I expected it to be.

SELECT * FROM sub_query_result HAVING quantity = MAX(quantity) always returns an empty result set

SELECT * FROM sub_query_result HAVING quantity = 42 is pointless since I need to know the exact max quantity in advance

SELECT * FROM sub_query_result WHERE quantity = MAX(quantity) simply works not ("Invalid use of group function")

---

---

I can see solutions that work but that I do not like -- due to extra actions I need to take on my back-end code that executes this sql request, or due to their inefficiency:

• I can create a temporary table, get max. quantity from it and place to a variable. Then I can use this variable inside the query to that temporary table and get the data I need.
• I can do

SELECT * FROM query_result HAVING quantity = (SELECT MAX(quantity) FROM 
   <Query upon sub-query upon another sub-query that shall return query_result>)

but that way I request the very same data twice! which in general is not a good approach.

---

---

So... Anything I missed? Any simple and elegant solutions that can solve my problem?

Answer 1

Order by quantity descending to get the max values first. Only select the first row, i.e. the one having the max values. ANSI SQL version:

SELECT * FROM query_result
ORDER BY quantity DESC
FETCH FIRST 1 ROW WITH TIES

WITH TIES means you can get several rows, if there are several rows having the same maximum quantity.