Post reply

Sorting - Custom Based.

  • December 10, 2012 at 5:04 am

    #271402

    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

  • December 10, 2012 at 5:09 am

    #1566985

    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

  • December 10, 2012 at 5:30 am

    #1566988

    Thaaaanks anthony

    Great.....!

  • December 10, 2012 at 5:35 am

    #1566989

    Hi anthony

    Is it Possible to get only single column name.

    namesortorder

    Smith1

    Jack2

    David3

    Ellen4

    john5

    Peter6

    sortorder column is not required

  • December 10, 2012 at 5:39 am

    #1566991

    wrap in a cte then just select the one column

  • December 10, 2012 at 5:44 am

    #1566993

    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, MVP, M.Sc (Comp Sci)
    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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply