Get the Top 3 record in same table.

Hi all,

I have some questions about sql statement. Is there anyway to get top 3 record for each item in the same table? I use before 'top' but it will give me the top value base on the price. Can someone give me an advice on this?

Thanks in advance.

Example:

Table 1

Item Name Price Date
A $10.00 15 Jan 2007
A $8.50 17 May 2006
A $8.00 1 Jan 2006
A $7.80 24 Sep 2005
B $12.20 2 Jan 2007
B $12.00 10 Oct 2006

I want get the result as below base on the example table 1. (Top3 Record for each item)

A $10.00 15 Jan 2007
A $8.50 17 May 2006
A $8.00 1 Jan 2006
B $12.20 2 Jan 2007
B $12.00 10 Oct 2006

select *
from tbl t
where price in (select top 3 price from tbl x where x.item_name = t.item_name)|||SELECT [Item Name], Price, Date
FROM (SELECT ROW_Number()OVER(PARTITION BY [Item Name] ORDER BY PRICE) as RowNum,[Item Name], Price, Date
FROM top3$)AS t1
WHERE RowNum<=3|||

Hi,

It really helps me solve my problem.

Thanks alot.