SQL Clone
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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8138 Visits: 1771
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 Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168763 Visits: 39530
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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8138 Visits: 1771
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 Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168763 Visits: 39530
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 Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168763 Visits: 39530
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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8138 Visits: 1771
Thanks.
scott.pletcher
scott.pletcher
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2140 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1717 Visits: 523
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