Selecting top two values for each set

Hi,

Is there a simple query to get the top two values for each type?
To clarify,

a_type a_name a_value
1 a1 10
1 a2 17
1 a3 2
2 b1 3
2 b2 18
2 b3 1
3 c1 8
3 c2 4
3 c3 12

I'd like to retrieve
1 a2 17
1 a1 10
2 b2 18
2 b1 3
3 c3 12
3 c1 8


Thanks

try this:

Latest two rows for each group
http://searchdatabase.techtarget.co...x285649,00.html
(registration may be required, but it's free)

rudy

Thanks

Here's another way to do it. It's pretty damn clunky though.

SELECT TOP 2 a_type, a_name, a_value
FROM t
WHERE a_type = 1
ORDER BY a_value DESC
UNION
SELECT TOP 2 a_type, a_name, a_value
FROM t
WHERE a_type = 2
ORDER BY a_value DESC
UNION
SELECT TOP 2 a_type, a_name, a_value
FROM t
WHERE a_type = 3
ORDER BY a_value DESC

you cannot have more than one ORDER BY in a UNION, and it has to go at the end

also, TOP will only work in microsoft access or sql server

Thanks for your replies. While I can use the TOP (mssql server) I don't know in advance how many records there are in column 'a_type' in advance.

Cheers
Darren