Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Sort SQL Results in Your Own Preferred Order Expand / Collapse
Author
Message
Posted Friday, July 23, 2010 12:53 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 822, Visits: 1,470
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.
Post #958185
Posted Friday, July 23, 2010 12:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 20,744, Visits: 32,560
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.



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)
Post #958191
Posted Friday, July 23, 2010 1:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 822, Visits: 1,470
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.
Post #958222
Posted Friday, July 23, 2010 1:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 20,744, Visits: 32,560
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?



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)
Post #958230
Posted Friday, July 23, 2010 1:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 20,744, Visits: 32,560
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;




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)
Post #958233
Posted Friday, July 23, 2010 1:42 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 12:11 PM
Points: 822, Visits: 1,470
Thanks.

Post #958235
Posted Friday, July 23, 2010 2:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, 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
Post #958252
Posted Monday, August 26, 2013 3:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:31 PM
Points: 161, Visits: 461
Hi, can I assume your query is for a report dataset ?
Post #1488509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse