Returning ordered result set, except first row

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning ordered result set, except first row

Author

Topic

loamguy
Starting Member

15 Posts

Posted - 2008-01-31 : 11:21:27

Hi all,

I feel like I'm missing something really simple here...

I'm trying to write an sp to return a list of countries alphabetically to populate a web drop-down list in a form. However, since most people using the form will be from USA, I want "USA" to appear as the first row, then the rest should be alphabetical, e.g. ("USA", "Afghanistan", "Albania"... "Zimbabwe")

I'm using a UNION query, but it's ordering the result set so that USA appears alphabetically, not as the first row. I'm not using an ORDER BY clause.

Here's the code I'm using:

CREATE PROCEDURE GetCountries AS

SELECT Country_Name
FROM Countries
WHERE Country_Name = 'USA'

UNION

SELECT Country_Name
FROM Countries
WHERE Country_Name <> 'USA'

GO

I also tried selecting into a temp table and doing a UNION that way, but got the same results.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-31 : 11:28:48

SELECT Country_Name
FROM ( SELECT 1 AS OrderId, Country_Name
FROM Countries
WHERE Country_Name = 'USA'

UNION

SELECT 2, Country_Name
FROM Countries
WHERE Country_Name <> 'USA' ) a
ORDER BY OrderId, Country_name

loamguy
Starting Member

15 Posts

Posted - 2008-01-31 : 11:33:09

quote:
Originally posted by jdaman

SELECT Country_Name
FROM ( SELECT 1 AS OrderId, Country_Name
FROM Countries
WHERE Country_Name = 'USA'

UNION

SELECT 2, Country_Name
FROM Countries
WHERE Country_Name <> 'USA' ) a
ORDER BY OrderId, Country_name

You sir, are a gentleman and a scholar. Worked like a charm.

Thanks a lot!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-31 : 18:53:33

Alternativly:

SELECT Country_Name
FROM Countries
ORDER BY
	CASE WHEN Country_Name = 'USA' THEN 1 ELSE 2 END,
	Country_Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources