Select Statement - 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

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-22 : 10:02:27

Hi,

I have the followinf select statement..

SELECT        - 1 AS OrganisationID, '--Please Select--' AS OrganisationName
UNION ALL
SELECT        OrganisationID, OrganisationName
FROM            tblOrganisation
ORDER BY OrganisationName

Results


OrganisationID           OrganisationName
22                       Animal
15                       Birds
-1                       --Please Select-- 
40                       Reptiles
36                       Snakes

I want the results to be;


OrganisationID           OrganisationName
-1                       --Please Select-- 
22                       Animal
15                       Birds
40                       Reptiles
36                       Snakes

How can I update my SQL select statement to yield these results..

Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 10:54:08

order by OrganisationID

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-22 : 11:22:26

order by OrganisationID - makes the OrganisationName not be in Order I want it to be...

I only want the first line to be

-- Please select --

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 11:47:08

then add a case to the order by

order by case OrganisationId
when -1 then char(0)
else OrganisationName
end

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-23 : 09:05:01

Ok - I have


SELECT        - 1 AS OrganisationID, '--Please Select--' AS OrganisationName
UNION ALL
SELECT        OrganisationID, OrganisationName
FROM            tblOrganisation
ORDER BY CASE OrganisationID WHEN - 1 THEN char(0) ELSE OrganisationName END

I receive the error -

Invalid column name 'OrganisationID'
Invalid column name 'OrganisationName'
ORDER BY items muct appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Thank you for resolving this for me

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-23 : 09:27:19

Yes, you would get those errors. You can't use aliases in the CASE statement in the ORDER BY. To get around it, wrap your selects in a subquery


SELECT        OrganisationID, OrganisationName
FROM (
	SELECT        - 1 AS OrganisationID, '--Please Select--' AS OrganisationName
	UNION ALL
	SELECT        OrganisationID, OrganisationName
	FROM            tblOrganisation
) q
ORDER BY CASE OrganisationID WHEN - 1 THEN char(0) ELSE OrganisationName END

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-23 : 09:33:23

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources