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

thanks very much for your help! i'll chew away on this solution for a bit. i realize it may be ambiguous (but in the real-world-problem that this example is an analogy for I have another weight column which will serve to break ties in popularity.) and if there is an exact tie including the weight, then I don't care which rows I get back so long as i get the proper number of them.

Thanks!

david

Thomas Kyte wrote:

> 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;
> Commit complete.
>
> 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

--
David Michaels <david_at_shockmarket.com>
Director of Technology
ShockMarket Corporation (650) 330-4665

Received on Fri Jun 25 1999 - 20:18:42 CDT