Page 1 of 1
Orber by
Posted: Wed Sep 08, 2004 10:10 am
by CerIs
Can you have 2 "ORDER BY" statements in a query? Eg at the moment i have "ORDER BY rand()" and this gets me a random set of rows. But i want to organise this by another field aswell. Is this possible?
Thanks
Posted: Wed Sep 08, 2004 10:21 am
by Wayne
.... ORDER BY col1, col2
Posted: Wed Sep 08, 2004 10:54 am
by CerIs
Hi, thanks.
I tried "ORDER BY rand(),Col1 DESC" but no luck.
Posted: Thu Sep 09, 2004 3:09 am
by CoderGoblin
Have you tried
Code: Select all
SELECT rand() as ordpos,col1,col2,col3 FROM table ORDER BY ordpos,col2 DESC;
Key is to put the rand() which I assume is the same as the Postgres RANDOM function into the output list as though it was a column.
Posted: Thu Sep 09, 2004 3:37 am
by CerIs
Code: Select all
SELECT rand() as ordpos,StarGrade,id,Hotelname FROM Hotels ORDER BY ordpos,StarGrade
This gets the random records ok but it doesnt order them. Also i have to put "Where `City` = 'Cityname' in there somewhere too.
Thanks for your help.Paul
Posted: Thu Sep 09, 2004 3:44 am
by CoderGoblin
Logic problem here I think,
rand() returns a random number so each row is a random number, this is unique, so the second order will order consist of one item.
Code: Select all
SELECT rand() as ordpos,StarGrade,id,Hotelname FROM Hotels ORDER BY StarGrade,ordpos
This will order first on StarGrade which I assume is 1-5 stars, followed by the random number.
Posted: Thu Sep 09, 2004 4:20 am
by CerIs
Hello, yes
Code: Select all
SELECT rand() as ordpos,StarGrade,id,Hotelname FROM Hotels ORDER BY StarGrade,ordpos limit 200
Only thing is i have to put a where clause in it. But when i try it it stops the random. eg
Code: Select all
SELECT rand() as ordpos,StarGrade,id,Hotelname FROM Hotels Where City ='London' ORDER BY StarGrade,ordpos limit 200
Thanks for the help.
Posted: Thu Sep 09, 2004 6:06 am
by CoderGoblin
Nothing wrong with the select as I see it... Stupid question...
Do you get results with
Code: Select all
SELECT StarGrade,id,Hotelname FROM Hotels Where City='London' ORDER BY StarGrade LIMIT 200
is city mixed content or all upper/lowercase?
Posted: Thu Sep 09, 2004 6:21 am
by CerIs
Yea this works
Code: Select all
SELECT StarGrade,id,Hotelname FROM Hotels Where City='London' ORDER BY StarGrade LIMIT 200
But theres no Random in it.
Posted: Thu Sep 09, 2004 6:29 am
by CoderGoblin
Sorry I'm stuck too then, works in postgres fine (changing rand() to random()),
Need to look for a MySql bod to answer if that is the database you are using
Posted: Thu Sep 09, 2004 6:54 am
by CerIs
No problem

Thanks for trying. Its annoying though becuase i can get Random working and ordering by Grade working just not at the same time
Thanks for your help.