July 23, 2010 at 12:53 pm
I know I've seen this somewhere before but just can't remember where it is and how to write it, basically here is my query:
Select 0, 'Select All' as FirstName
Union
SELECT distinct u.ID, u.FirstName + ' ' + u.LastName as [Name]
from Eventlog e
inner join u on e.UserLogon = u.[login]
order by (case FirstName when 'Select All' then 1 else 0 end) asc
The case is not working.
What I want is the "Select All' appears in the first result.
Thanks.
July 23, 2010 at 12:59 pm
I think I know what you are trying to do, but to be sure could you post the DDL (CREATE TABLE) statement(s) for the table(s), some sample data (as a series of INSERT INTO statements), and expected results. I'm guessing only about 3 or 4 rows of sample data.
July 23, 2010 at 1:25 pm
Thank you in advance, here is a sample:
CREATE TABLE tbl
(
ID int,
[Name] varchar(50)
)
insert into tbl (ID, [Name]) values(1, 'John Doe')
insert into tbl (ID, [Name]) values(2, 'John Smith')
insert into tbl (ID, [Name]) values(3, 'Jane Doe')
insert into tbl (ID, [Name]) values(4, 'Bill Clinton')
insert into tbl (ID, [Name]) values(5, 'Hello Kitty')
My expected result would be 'Select All' on top of the ordered result.
July 23, 2010 at 1:38 pm
halifaxdal (7/23/2010)
Thank you in advance, here is a sample:
CREATE TABLE tbl
(
ID int,
[Name] varchar(50)
)
insert into tbl (ID, [Name]) values(1, 'John Doe')
insert into tbl (ID, [Name]) values(2, 'John Smith')
insert into tbl (ID, [Name]) values(3, 'Jane Doe')
insert into tbl (ID, [Name]) values(4, 'Bill Clinton')
insert into tbl (ID, [Name]) values(5, 'Hello Kitty')
My expected result would be 'Select All' on top of the ordered result.
It would help if you showed us the desired results. I understand you want 'Select All' on top, but how should the other names be sorted; by name or ID?
July 23, 2010 at 1:42 pm
Here is one way of doing this in SQL Server 2005:
CREATE TABLE dbo.TestTable
(
ID int,
[Name] varchar(50)
) ;
insert into dbo.TestTable (ID, [Name]) values(1, 'John Doe') ;
insert into dbo.TestTable (ID, [Name]) values(2, 'John Smith');
insert into dbo.TestTable (ID, [Name]) values(3, 'Jane Doe') ;
insert into dbo.TestTable (ID, [Name]) values(4, 'Bill Clinton');
insert into dbo.TestTable (ID, [Name]) values(5, 'Hello Kitty');
with NameList as (
select
0 as ID,
'Select All' as UName
union
select
ID,
[Name]
from
dbo.TestTable
)
select
UName
from
NameList
order by
ID;
July 23, 2010 at 1:42 pm
Thanks.
July 23, 2010 at 2:02 pm
order by (case FirstName when 'Select All' then 1 else 0 end) asc
What I want is the "Select All' appears in the first result.
Then wouldn't you want:
order by (case FirstName when 'Select All' then 0 else 1 end) asc
🙂
Of course that may not help with this specific query because I think you can't reference an "alias" in an ORDER BY with a UNION.
Scott Pletcher, SQL Server MVP 2008-2010
August 26, 2013 at 3:26 pm
Hi, can I assume your query is for a report dataset ?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply