How to Sort SQL Results in Your Own Preferred Order

• halifaxdal

  SSCoach

  Points: 19741

  More actions

  July 23, 2010 at 12:53 pm

  #235687

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 23, 2010 at 12:59 pm

  #1197697

  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.

• halifaxdal

  SSCoach

  Points: 19741

  More actions

  July 23, 2010 at 1:25 pm

  #1197724

  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

  SSC Guru

  Points: 442467

  More actions

  July 23, 2010 at 1:38 pm

  #1197730

  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

  SSC Guru

  Points: 442467

  More actions

  July 23, 2010 at 1:42 pm

  #1197733

  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;

• halifaxdal

  SSCoach

  Points: 19741

  More actions

  July 23, 2010 at 1:42 pm

  #1197735

  Thanks.

• scott.pletcher

  SSCertifiable

  Points: 5958

  More actions

  July 23, 2010 at 2:02 pm

  #1197750

  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

  SSCarpal Tunnel

  Points: 4343

  More actions

  August 26, 2013 at 3:26 pm

  #1644977

  Hi, can I assume your query is for a report dataset ?