0

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?
Aliaksandr
  • 3
  • 1

1 Answers1

0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • My outdated MySQL refused to eat this query, but freshly installed MariaDB accepted it and gave me the exact thing I needed. Thanks a lot! – Aliaksandr Mar 18 '22 at 16:35