[SOLVED] Control what is displayed first?

[kevin_newbie]

Hello,

 

What is the best way to display an entry (Mysql and php) first when even though you have it set to alphabetical order?

 

For example i current have the list of a few states as followed;

 

Alabama

Alaska

Arizona

Arkansas

California

Colorado

 

But I want to be able to put California first then the rest follow so like this:

 

California

Alabama

Alaska

Arizona

Arkansas

Colorado

 

What is the best way to achieve that?

 

Thanks

[kickstart]

Hi

 

Depends how many you want like that and how flexible.

 

Personally I would add an extra column for primary sort order. Set the ones you want to appear first to have a sort order of 1 and the rest to 2 (or any other pair of numbers). Sort by that and then the name.

 

You could actually do it with a case statement within the SQL to generate an extra column for the sort order and recognise California, but while that would avoid having to do a simple database change it would be clunky and a pain to modify when you suddenly want (say) Texas to appear at the top as well .

 

All the best

 

Keith

[Mchl]

Or do a UNION of two selects, first of which selects California only, and the second all other states.

[kevin_newbie]

kickstart - It's only going to be california, I am not going to change it.

 

mchl - What do you mean a union of two selects? I haven't done anything like this (Union).

[Mchl]

http://www.w3schools.com/sql/sql_union.asp

[kevin_newbie]

Mchl - That makes sense what union does now, but I only have one table not two.

 

kickstart - I went the route of adding another column and having everything have 2 while california have 1. When i do the query though it does california first then everything else not in alphabetical order?

 

SELECT id,name,orderList FROM states ORDER BY orderList ASC

 

I'm I missing something?

 

Thanks

[Mchl]

You can UNION one table to itself. No problem with that

[kevin_newbie]

Mchl,

 

So from what you are saying I can, i tried but it's not doing it;

 

SELECT id,name,orderList FROM states WHERE orderList = 1
UNION
SELECT id,name,orderList FROM states ORDER BY name ASC

[Mchl]

Try:

 

SELECT id,name,orderList FROM states WHERE orderList = 1
UNION
(SELECT id,name,orderList FROM states ORDER BY name ASC)

 

[kickstart]

Hi

 

Using the extra column you could do:-

 

SELECT id,name,orderList FROM states ORDER BY orderList , name ASC

 

Using a UNION you could do:-

 

SELECT id,name, 1 AS isCal FROM states WHERE name = "California"

UNION

SELECT id,name, 2 AS isCal FROM states WHERE name != "California"

ORDER BY isCal, name ASC

 

Personally I would prefer using the extra column as it means you do not have data in the code, but either are valid.

 

All the best

 

Keith

[kevin_newbie]

Thank you guys very much. Both methods worked