Query question

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
User avatar
Uranium-235
Forum Newbie
Posts: 13
Joined: Tue Aug 08, 2006 3:57 pm

Query question

Post by Uranium-235 »

I have a very big table, one of the fields, can be one of 3 things (basically, this table is a job listing, and this perticular field is the employment agency office).

Is there any way, to in one query select *, of a perticular office first, then display every other office after that, alphabetically? It's hard to explain

Like I want Office 2's jobs displayed first, then I would want Office 1, then 3

or 3, then 1 and 2

do you get the idea? I know a way of doing this with multiple queries, but with how things are outputted in the script, that would be a serious pain. Is there any way to achieve this with one query?

thank you
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

ORDER BY clause.
User avatar
Uranium-235
Forum Newbie
Posts: 13
Joined: Tue Aug 08, 2006 3:57 pm

Post by Uranium-235 »

I thought you could only order by fields

how can I order by, if a field is a certain value?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

$sql = "SELECT id,office_name FROM jobs WHERE officeID='$officeID' ORDER BY office_name";
(#10850)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can order by more than just fields. Provided the database can understand the expression it will try to honor it. Whether it affects the results or not, that's a different story. :)
User avatar
Uranium-235
Forum Newbie
Posts: 13
Joined: Tue Aug 08, 2006 3:57 pm

Post by Uranium-235 »

arborint wrote:$sql = "SELECT id,office_name FROM jobs WHERE officeID='$officeID' ORDER BY office_name";
woulden't that just select the first office? what about all the other jobs with a different office then 'officeID' (actually this is just by name, but still)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Here's a big ..push

Code: Select all

ORDER BY `officeID` = '2' DESC, `office_name`
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Uranium-235 wrote:
arborint wrote:$sql = "SELECT id,office_name FROM jobs WHERE officeID='$officeID' ORDER BY office_name";
woulden't that just select the first office? what about all the other jobs with a different office then 'officeID' (actually this is just by name, but still)
Yes this will only grab one office. You may need to look at a union query or a subselect query, or some other regex type query where you pull the office you want first, order it by whatever, then pull all the others that are not the first office and order that by whatever. Sounds uniony to me.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

I'm not an sql mastah by any stretch, as a result, i would probably just use two queries, the first to obtain the office in question, and the second to get the rest...
Post Reply