Organizing my selections

[BrimReap]

Hi,

Unfortunately, I have a field in a database that is labeled state_province. It contains state abbreviations such as NY for New York. It also contains Provinces like Alberta.

How can you arrange that data when i select it?

I'd like to have all the US states at the top of my list in alphabetical order and have the provinces at the end of my list in alphabetical order.

I used

SELECT DISTINCT state_province from table
ORDER by state_province

but it just alphabetizes everything.

Thanks

 

[bperry]

From this information, it doesn't seem like this will be possible. Ideally, you would have some kind of country column in the table as well:

SELECT DISTINCT state_province from table
ORDER by country_code, state_province

(This would sort Canada ahead of US, but there are ways to deal with that too.)


Perhaps there's a country/state lookup table that you could pull the country from? (Maybe you can make this table?)

SELECT DISTINCT state_province from table t
Inner Join LookupTable l
ON t.state_province = l.state_province
ORDER by l.country, t.state_province
ORDER by l.country_code, t.state_province

I suspect you've already looked at these options. Perhaps
other forum readers will think of a suggestion.

bp

 

[cmmrfrds]

IF the state abbreviation is always 2 characters and the province name is never 2 characters then put a case statement in the order by clause. In this example the states will sort ahead of the provinces. I didn't run this so you may need to play with the syntax.

order by case stateprovince
when (len(stateprovince)=2) then 1
else 2
end,
stateprovince

 

[bperry]

That's a good suggestion by cmmrfrds, if that's the way the abbreviations are stored in the table. Officially, province abbreviations are only 2 characters, just like states. But sometimes you find them stored with more, like ONT for Ontario instead of ON. Unfortunately, though, some provinces are just 2 characters no matter how you slice 'em. NS=Nova Scotia, BC=British Columbia, etc

It was a good thought, though.

bperry from Nova Scotia

 

[bperry]

BrimReap:
I re-read this and now see more clearly what cmmrfrds was getting at. I was assuming that province abbreviations are stored in your table, while cmmrfrds takes from your message (perhaps rightly) that states are abbreviated while provinces are spelled out fully. Which interpretation is right?

bp