Help ordering MySQL query results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
csingsaas
Forum Newbie
Posts: 22
Joined: Thu Jul 06, 2006 9:34 pm
Location: Edina, MN

Help ordering MySQL query results

Post by csingsaas »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags [u]where appropriate[/u] when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I the following query built:

[syntax="sql"]SELECT horse_id, horse_name, horse_breed, horse_registered, horse_sex, horse_color, horse_foaldate, horse_city, horse_state, horse_price FROM HORSE_ADS WHERE horse_state = 'MN' ORDER BY RAND()
There is a field in HORSE_ADS called "horse_adtype". There are three ad types, one is a featured advertisement, another is a typical photo advertisement, and the last is a free advertisement.

I want to give priority (top of results) to featured ads, second tier priority to photo ads, and want to place at the bottom free advertisements.

How can I affect the order the results are put in and at the same time randomly order them within their group?.


1) List randomly ordered Featured ads
2) List randomly ordered Photo ads
3) List randomly ordered Free ads[/syntax]


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags [u]where appropriate[/u] when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

have a column in your table to show the priority of the ad, and then sort your query with rand(), `priority`
csingsaas
Forum Newbie
Posts: 22
Joined: Thu Jul 06, 2006 9:34 pm
Location: Edina, MN

Post by csingsaas »

No other way? I was hoping I could define the order something like:

Code: Select all

ORDER BY horse_adtype('F','P','X')
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

There might be a better way of doing this, but here's the quick and sloppy way:

Code: Select all

(SELECT * FROM HORSE_ADS WHERE horse_adtype = 'featured' ORDER BY RAND())
UNION
(SELECT * FROM HORSE_ADS WHERE horse_adtype = 'photo' ORDER BY RAND())
UNION
(SELECT * FROM HORSE_ADS WHERE horse_adtype = 'free' ORDER BY RAND())
Here's the UNION syntax: http://dev.mysql.com/doc/refman/5.0/en/union.html
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Make a horse_adtypes table, give each of the three adtypes an numerical id and a description, then change the text adtype field to a numerical value that relates to the adtype table. Order your result by the numerical values in HORSE_ADS and join that in the query to adtypes.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Jcart wrote:have a column in your table to show the priority of the ad, and then sort your query with rand(), `priority`
Wouldn't it be ORDER BY `priority`, rand()?
Post Reply