union all and order by problemo

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)

union all and order by problemo

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2006-08-16 : 08:19:03

SELECT *
FROM tblCountry
WHERE Country_Id = 26
UNION ALL
SELECT *
FROM tblCountry
WHERE Country_Id <> 26

--order by country_Name

i need to select country_id =26 and then the rest i want them ordered by name.
if i put order by country_name, the country_id 26 isn't displayed as the first one.
is there anyway to apply the order by only to the second select not the whole?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 08:42:08

[code]SELECT *
FROM tblCountry
ORDER BY CASE WHEN Country_ID = 26 THEN 0 ELSE 1 END,
Country_Name[/code]
Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-16 : 08:49:34

[code]
Select * From
(
Select Top 100 Percent *,Country_Name as CountryName From tblCountry
WHERE Country_Id = 26
Union All
Select Top 100 Percent *,Country_Name From tblCountry
WHERE Country_Id = 26 order by Country_Name
) as F
[/code]

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 08:57:20

Chirag, change the last Country_Id = 26 to Country_Id <> 26. And after a clean-up this does the same thing

SELECT	f.Country_Name
FROM	(
		SELECT	Country_Name
		FROM	tblCountry
		WHERE	Country_ID = 26

		UNION ALL

		SELECT		TOP 100 PERCENT Country_Name
		FROM		tblCountry
		WHERE		Country_ID <> 26
		ORDER BY	Country_Name
	) f

But it still looks somewhat overworked...

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-16 : 09:00:47

quote:
Originally posted by Peso

Chirag, change the last Country_Id = 26 to Country_Id <> 26. And after a clean-up this does the same thing
SELECT f.Country_Name
FROM (
SELECT Country_Name
FROM tblCountry
WHERE Country_ID = 26

UNION ALL

SELECT TOP 100 PERCENT Country_Name
FROM tblCountry
WHERE Country_ID <> 26
ORDER BY Country_Name
) f
But it still looks somewhat overworked...

Peter Larsson
Helsingborg, Sweden

Opps Copy paste

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources