Mysql - Selecting 3 best per condition

[jaruba]

I have a table formed like this:

id | grp | mv | vt
------------------
1 | 1 | 2 | 8
2 | 1 | 4 | 3
3 | 2 | 3 | 5
4 | 1 | 6 | 10
5 | 2 | 1 | 34
6 | 3 | 9 | 1
7 | 1 | 10| 70
..

I'm kind of new to mysql.. and what I need to do is select 3 rows with ORDER BY vt DESC for each condition like grp='3' for example. Is there any way to do this with only one mysql query?

For grp='1' and grp='2', the result I'm hoping for would be:

id | grp | mv | vt
------------------
7 | 1 | 10| 70
4 | 1 | 6 | 10
1 | 1 | 2 | 8
5 | 2 | 1 | 34
3 | 2 | 3 | 5

Thank you in advance for any help.

[Demaestro]

try this:

Select * from table where grp="3" order by vt desc limit 3

For more than one where clause it would be like this.

*****Execute as 1 statement*******
Select * from table where grp="1" order by vt desc limit 3
UNION
Select * from table where grp="2" order by vt desc limit 3
************END************

Play around with those and see if you are getting the desired results.

Post back if you need.

[Demaestro]

FYI:

You can do more than 2 unions

Just make sure you have a UNION between each query.

Select * from table where grp="1" order by vt desc limit 3
UNION
Select * from table where grp="2" order by vt desc limit 3
UNION
Select * from table where grp="3" order by vt desc limit 3

This is the only way to limit each grp to 3 rows though and have it returned in 1 query.

[jaruba]

Thank you for the quick reply, this should be exactly what I needed.