Simple Query with a Twist - 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 2008 Forums

Transact-SQL (2008)

Simple Query with a Twist

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-08-07 : 15:09:09

Good afternoon, I have a simple table with names in it. I would like to give the users a new option at the bottom of the list of 'Other'

select nc_displayname from NCOS_DomainUser
order by NC_DisplayName

and then the last entry in the list would be 'Other'

Any ideas, thanks in advance.

Bryan Holmstrom

watersa8
Starting Member

2 Posts

Posted - 2013-08-07 : 15:19:08

I take it its not time critical? just union other onto the end of the query

or

create a temp table insert in the stuff from ur select then insert in other then return the temp table.

There's other ways to do it but there 2 quick and easy ones.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 15:35:10

If you want to order the set so "Other" shows up at the very end, do it like this:

SELECT nc_displayname FROM (
select nc_displayname from NCOS_DomainUser
UNION ALL SELECT 'Other') s
order by CASE WHEN NC_DisplayName = 'Other' THEN 1 ELSE 0 END

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-08-07 : 16:24:02

Perfect, I'm going to use James solution. But thank you both

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 16:41:28

You are very welcome - glad to help.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-07 : 17:47:55

You must have the full sort in the outer ORDER BY to guarantee a properly sorted result. "UNION ALL" would output two 'Other' values if somehow 'Other' were a value in the table, so "UNION" seems better to me.



select nc_displayname
from (
    select nc_displayname 
    from dbo.NCOS_DomainUser
    union
    select 'other'
) as derived
order by
    case when nc_displayname = 'Other' then 0 else 1 end,
    nc_displayname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources