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.