tricky SQL question: how to limit num rows returned?

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 - 18:38:56 CDT