| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: tricky SQL question: how to limit num rows returned?
A copy of this was sent to David Michaels <david_at_shockmarket.com>
(if that email address didn't require changing)
On Thu, 24 Jun 1999 16:38:56 -0700, you wrote:
>I want to make a query and limit my rows returned to , say, 2.
>However, I don't want 2 total rows returned, but 2 rows returned per
>category, where category is a column in the table. naturally I'm also
>specifying an order by clause to determine the order in which rows will
>be returned (and therefore which rows will be chopped off by my
>specified limit of 2).
>
>for example, if my table looks like:
>
>food_type name popularity
>--------- ------- ----------
>candy m&m 5
>candy snickers 4
>candy crunch 3
>entree burger 5
>entree pizza 4
>entree rice 3
>
>I want a select statement that returns the 2 most popular rows of each
>food type
>
well, yes and no. The question is ambiguous -- what happens when there are 3 rows with popularity = 5? What happens when there is 100 rows with popularity = 5?
Do you want the rows such that the count of rows with a popularity GREATER then that row is less then 3? If so:
SQL> select *
2 from t a
3 where 2 > ( select count(*)
4 from t b 5 where b.popularity > a.popularity 6 and b.food_type = a.food_type )7 /
gets it. Here is an example (shows what happens when you have duplicate popularities as well)
SQL> create table t (food_type varchar2(25), name varchar2(25), popularity
number);
Table created.
SQL> insert into t values ( 'candy','m&m', 5 ); SQL> insert into t values ( 'candy','snickers', 4 ); SQL> insert into t values ( 'candy','crunch', 3 ); SQL> insert into t values ( 'entree','burger', 5 ); SQL> insert into t values ( 'entree','pizza', 4 ); SQL> insert into t values ( 'entree','rice', 3 ); SQL> commit;
SQL> select *
2 from t a
3 where 2 > ( select count(*)
4 from t b 5 where b.popularity > a.popularity 6 and b.food_type = a.food_type )7 /
FOOD_TYPE NAME POPULARITY ------------------------- ------------------------------ ---------- candy m&m 5 candy snickers 4 entree burger 5 entree pizza 4 SQL> insert into t values ( 'candy','lemon drops', 5 );1 row created.
SQL> select *
2 from t a
3 where 2 > ( select count(*)
4 from t b 5 where b.popularity > a.popularity 6 and b.food_type = a.food_type )7 /
FOOD_TYPE NAME POPULARITY ------------------------- ------------------------------ ---------- candy m&m 5 entree burger 5 entree pizza 4 candy lemon drops 5 SQL> insert into t values ( 'candy','chocolate bars', 5 );1 row created.
SQL> select *
2 from t a
3 where 2 > ( select count(*)
4 from t b 5 where b.popularity > a.popularity 6 and b.food_type = a.food_type )7 /
FOOD_TYPE NAME POPULARITY ------------------------- ------------------------------ ---------- candy m&m 5 entree burger 5 entree pizza 4 candy lemon drops 5 candy chocolate bars 5
SQL> See -- now there are 3 matches for Candy and no easy way to get just 2 (there are ways but they aren't easy or fast)
>can this be done in a single SQL statement?
>
>much thanks,
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 25 1999 - 06:31:35 CDT
 |
 |