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

Sorting - Custom Based. Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 5:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
Hi Team,


Is it possible to sort records in custom.

Eg:

i've below records in a table.
john
Peter
smith
Ellen
Jack
David

i want in below order

Smith
Jack
David
Ellen
john
Peter
Post #1394554
Posted Monday, December 10, 2012 5:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 5,227, Visits: 5,087
If you have some way of telling SQL the sort order then yes

declare @table table (name varchar(10))
insert into @table values ('Smith'),
('Jack'),
('David'),
('Ellen'),
('john'),
('Peter')

select * from @table order by name asc
select * from @table order by name desc
select *, case name when 'Smith' then 1 when 'jack' then 2 when 'david' then 3 when 'ellen' then 4 when 'john' then 5 when 'peter' then 6 end as sortorder from @table order by sortorder





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1394556
Posted Monday, December 10, 2012 5:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
Thaaaanks anthony

Great.....!
Post #1394559
Posted Monday, December 10, 2012 5:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
Hi anthony

Is it Possible to get only single column name.

name sortorder
Smith 1
Jack 2
David 3
Ellen 4
john 5
Peter 6

sortorder column is not required
Post #1394561
Posted Monday, December 10, 2012 5:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 5,227, Visits: 5,087
wrap in a cte then just select the one column



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1394563
Posted Monday, December 10, 2012 5:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:38 PM
Points: 42,845, Visits: 35,975
If those 2 columns are in a table, then

SELECT name FROM SomeTable ORDER BY sortorder

No CTE required



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1394565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse