Welcome to WebmasterWorld Guest from 54.167.216.93

Forum Moderators: open

Message Too Old, No Replies

Mysql - Selecting 3 best per condition

     
6:14 pm on May 18, 2010 (gmt 0)

5+ Year Member



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.
6:51 pm on May 18, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
6:57 pm on May 18, 2010 (gmt 0)

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
7:06 pm on May 18, 2010 (gmt 0)

5+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month