Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
haoli12345
Starting Member
2 Posts |
 Posted - 2007-09-13 : 11:46:39
|
| I have a table that list Canadian provinces and American States it looks something like this:ID | ProvState Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvStateUNIONSELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvStateAnyone have any suggestions to this problem? |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-09-13 : 11:51:38
|
| Hi,Try this SELECT * FROM SPProvinceORDER BY CASE WHEN ID < 14 THEN 1 ELSE 2 END, ProvState |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-13 : 11:52:22
|
[code]SELECT *FROM SPProvinceORDER BY CASE WHEN ID < 14 THEN 0 ELSE 1 END, ProvState[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
haoli12345
Starting Member
2 Posts |
Posted - 2007-09-13 : 13:45:12
|
| WOW you guys are awesome thanks. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Two select statements