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

Yes, but unfortunately I don't know all the possible "food_types" in advance - (and I know I can make a seperate query to get these but want to get it in one shot if possible)

David

Conrad Chan wrote:

> How about the following...
>
> Assume your table does have a unique key, i.e. food_id
>
> SELECT * FROM food WHERE food_id in (
> SELECT food_id FROM food WHERE food_type='candy' AND rownum <= 2
> UNION
> SELECT food_id FROM food WHERE food_type='entree' AND rownum <= 2
> )
>
> Conrad
>
> David Michaels <david_at_shockmarket.com> wrote in message
> news:3772C190.E7897EC3_at_shockmarket.com...
> > 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
> >
> > can this be done in a single SQL statement?
> >
> > much thanks,
> >
> > --
> > David Michaels <david_at_shockmarket.com>
> > Director of Technology
> > ShockMarket Corporation (650) 330-4665
> >
> >

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

Received on Thu Jun 24 1999 - 22:59:37 CDT