Welcome to WebmasterWorld Guest from 54.147.134.218

Forum Moderators: open

Message Too Old, No Replies

Mysql - Selecting 3 best per condition

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

New User

5+ Year Member

joined:May 12, 2009
posts: 21
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2606
votes: 0


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)

New User

5+ Year Member

joined:May 12, 2009
posts:21
votes: 0


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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members