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
Go to Top of Page

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 --
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-10-23 : 09:33:23
Thank you
Go to Top of Page
   

- Advertisement -