Re: tricky SQL question: how to limit num rows returned?

From: <andrew_at_soft-solutions-inc.com>
Date: Fri, 25 Jun 1999 15:02:53 GMT
Message-ID: <377398cc.3521984@news.ufl.edu>

Man, this was a tricky one. I have a solution for you, as long as the following 2 assumptions hold true:
1) there are unique values for popularity within food_type 2) You only want the top 2 popularity values for each food type. If you decide you want the top 3, this solution breaks down.

Here is the query I came up with and the results:

SQL> SELECT A.FOOD_TYPE, A.DESCR, A.POPULARITY   2 FROM FOOD A
  3 WHERE A.POPULARITY =
  4 (SELECT MAX(A1.POPULARITY) FROM FOOD A1   5 WHERE A1.FOOD_TYPE = A.FOOD_TYPE)   6 UNION
  7 SELECT B.FOOD_TYPE, B.DESCR, B.POPULARITY   8 FROM FOOD B
  9 WHERE B.POPULARITY =
 10 (SELECT MAX(B1.POPULARITY) FROM FOOD B1

 11     WHERE B1.FOOD_TYPE = B.FOOD_TYPE
 12       AND B1.POPULARITY <>
 13        (SELECT MAX(B2.POPULARITY) FROM FOOD B2
 14       WHERE B2.FOOD_TYPE = B1.FOOD_TYPE))

 15 ORDER BY 1,3 DESC
 16 ;

FOOD_TYPE  DESCR                POPULARITY
---------- -------------------- ----------
APPETIZER  SHRIMP COCKTAIL               5
APPETIZER  CHEESE STICKS                 4
CANDY      M-N-M                         5
CANDY      SNICKERS                      4
ENTREE     BURGER                        5
ENTREE     PIZZA                         4

6 rows selected. Received on Fri Jun 25 1999 - 10:02:53 CDT