order by - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

order by

Author

Topic

adiki
Starting Member

1 Post

Posted - 2004-04-14 : 01:15:22

Hi,

I need to wirte a query for obtaining the following output

country_id country_name
----------- -------------
120 USA
119 UK
101 ARGENTINA
102 ALABAMA
103 BANGLADESH
104 BRUNEI
105 CANADA
---- AND GOES ON IN ASCENDING ORDER

The first two rows will always be USA and UK and the rest of
countries should be in ascending order.

any solution!!!

Regards
Srini

gpl
Posting Yak Master

195 Posts

Posted - 2004-04-14 : 06:21:47

Add a new BIT column PriorityCountry, make it 1 for UK and USA, 0 for all others then sort on PriorityCountry DESC and country_name ASC

This has the 'benefit' of putting UK first too <vbg/>

Graham

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-14 : 06:22:46

investigate (ie search here) ORDER BY + CASE....you'll find some examples that will teach you how to solve this problem....

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-14 : 06:51:23

Hey! Whattabout Norway??

kroky
Starting Member

14 Posts

Posted - 2004-04-14 : 07:09:23

Hey! Whattabout Norway??

this post rox....

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-14 : 09:29:18

quote:
Originally posted by gpl

Add a new BIT column PriorityCountry, make it 1 for UK and USA, 0 for all others then sort on PriorityCountry DESC and country_name ASC

This has the 'benefit' of putting UK first too <vbg/>

Graham

or make PriorityCountry 2 for USA and 1 for UK and 0 for all else, then sort descending...

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-14 : 09:33:34

[code]
USE Northwind
GO

CREATE TABLE myTable99(country_id int, country_name varchar(255))
GO

INSERT INTO myTable99(country_id, country_name)
SELECT 120, 'USA' UNION ALL
SELECT 119, 'UK' UNION ALL
SELECT 101, 'ARGENTINA' UNION ALL
SELECT 102, 'ALABAMA' UNION ALL
SELECT 103, 'BANGLADESH' UNION ALL
SELECT 104, 'BRUNEI' UNION ALL
SELECT 105, 'CANADA' UNION ALL
SELECT 106, 'NORWAY'

SELECT *
FROM myTable99
ORDER BY CASE WHEN Country_Name = 'USA' THEN 1
WHEN Country_Name = 'UK' THEN 2
ELSE 3
END
, Country_Name
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources