Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Sort SQL Results in Your Own Preferred Order


How to Sort SQL Results in Your Own Preferred Order

Author
Message
halifaxdal
halifaxdal
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1683
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 [user] 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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24187 Visits: 37955
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
halifaxdal
halifaxdal
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1683
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24187 Visits: 37955
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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24187 Visits: 37955
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;



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
halifaxdal
halifaxdal
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1683
Thanks.
scott.pletcher
scott.pletcher
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 473

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
kevin_nikolai
kevin_nikolai
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 511
Hi, can I assume your query is for a report dataset ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search