Page 1 of 1

Can you ORDER BY specific results in a Field?

Posted: Fri Sep 10, 2010 3:09 am
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?

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

Posted: Fri Sep 10, 2010 7:52 am
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

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

Posted: Fri Sep 10, 2010 1:05 pm
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!

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

Posted: Fri Sep 10, 2010 1:20 pm
by simonmlewis
Thank you but the quote before did the trick perfectly.
Thanks everyone.

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

Posted: Sun Oct 03, 2010 9:58 am
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');

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

Posted: Wed Feb 16, 2011 4:22 am
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, * ?

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

Posted: Wed Feb 16, 2011 5:54 am
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

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

Posted: Wed Feb 16, 2011 7:45 am
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.

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

Posted: Wed Feb 16, 2011 7:58 am
by simonmlewis
Perfect - both work for me. So it's just a matter of choice.