Can you ORDER BY specific results in a Field?

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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Can you ORDER BY specific results in a Field?

Post by simonmlewis »

Code: Select all

SELECT name, species, birth FROM pet ORDER BY species, birth DESC
This sorts by one field, then by another.

I want to be able to sort by one field, but showing a specific result from that field, then another.

For example: if Species had CAT, DOG, HUMAN in it, I want to show the products in order, but first show all HUMAN, then CAT and then DOG.

Is this simple?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Can you ORDER BY specific results in a Field?

Post by VladSun »

If there are only 3 types of species, why don't you make them integer instead of string typed?

You may use conditioning in the ORDER BY clause to solve your problem:

Code: Select all

ORDER BY 
    species='HUMAN' DESC,
    species='CAT' DESC,
    species='DOG' DESC
There are 10 types of people in this world, those who understand binary and those who don't
lavaeagle
Forum Newbie
Posts: 21
Joined: Thu Sep 09, 2010 1:41 pm

Re: Can you ORDER BY specific results in a Field?

Post by lavaeagle »

Code: Select all


$spe1 = mysql_query("SELECT * FROM pet ORDER BY species DESC");

while($spe1_fa = mysql_fetch_assoc($spe1)){
echo "$spe1_fa['species'] - $spe1_fa['name'] - $spe1_fa['birth']";
}

I believe this is what you meant, it shows everything in the table by the species name and all of it's information. If there is anything I can clarify please let me know!
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you ORDER BY specific results in a Field?

Post by simonmlewis »

Thank you but the quote before did the trick perfectly.
Thanks everyone.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Can you ORDER BY specific results in a Field?

Post by Darhazer »

VladSun wrote:If there are only 3 types of species, why don't you make them integer instead of string typed?

You may use conditioning in the ORDER BY clause to solve your problem:

Code: Select all

ORDER BY 
    species='HUMAN' DESC,
    species='CAT' DESC,
    species='DOG' DESC
It's the same as

Code: Select all

ORDER BY FIELD(species, 'HUMAN', 'CAT', 'DOG');
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you ORDER BY specific results in a Field?

Post by simonmlewis »

hi again
just picking up on this as I have a new query, but it is related directly with this.

If I want to do a sort, and it's on location. So you have London, New York, Boston, Sydney as the possible results.

But I want to show all result on London FIRST.... then the rest in say DATE order? How would I do that?

Is it sort by London, * ?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Can you ORDER BY specific results in a Field?

Post by Darhazer »

simonmlewis wrote:hi again
just picking up on this as I have a new query, but it is related directly with this.

If I want to do a sort, and it's on location. So you have London, New York, Boston, Sydney as the possible results.

But I want to show all result on London FIRST.... then the rest in say DATE order? How would I do that?

Is it sort by London, * ?
ORDER BY `city` = 'London' DESC, `date` DESC
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Can you ORDER BY specific results in a Field?

Post by Weirdan »

Darhazer wrote:ORDER BY `city` = 'London' DESC, `date` DESC
or rather ORDER BY city="London" DESC, city DESC, date DESC
This is like 'order by city, date', but puts London first.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you ORDER BY specific results in a Field?

Post by simonmlewis »

Perfect - both work for me. So it's just a matter of choice.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply