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

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
>
>
Received on Thu Jun 24 1999 - 22:50:32 CDT