Page 1 of 1

Help ordering MySQL query results

Posted: Sun Nov 26, 2006 9:39 pm
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]

Posted: Mon Nov 27, 2006 12:18 am
by John Cartwright
have a column in your table to show the priority of the ad, and then sort your query with rand(), `priority`

Posted: Mon Nov 27, 2006 7:08 am
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')

Posted: Mon Nov 27, 2006 9:43 am
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

Posted: Mon Nov 27, 2006 11:05 am
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.

Posted: Mon Nov 27, 2006 11:34 am
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()?