Orber by

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
CerIs
Forum Newbie
Posts: 22
Joined: Sat May 29, 2004 9:20 am

Orber by

Post 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
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

.... ORDER BY col1, col2
CerIs
Forum Newbie
Posts: 22
Joined: Sat May 29, 2004 9:20 am

Post by CerIs »

Hi, thanks.

I tried "ORDER BY rand(),Col1 DESC" but no luck.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
CerIs
Forum Newbie
Posts: 22
Joined: Sat May 29, 2004 9:20 am

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
CerIs
Forum Newbie
Posts: 22
Joined: Sat May 29, 2004 9:20 am

Post by CerIs »

Hello, yes

Code: Select all

SELECT rand() as ordpos,StarGrade,id,Hotelname FROM Hotels  ORDER BY StarGrade,ordpos limit 200
:o :o

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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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?
CerIs
Forum Newbie
Posts: 22
Joined: Sat May 29, 2004 9:20 am

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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
CerIs
Forum Newbie
Posts: 22
Joined: Sat May 29, 2004 9:20 am

Post by CerIs »

No problem 8) Thanks for trying. Its annoying though becuase i can get Random working and ordering by Grade working just not at the same time :roll:

Thanks for your help.
Post Reply