Post reply

customized sort order

  • June 13, 2005 at 1:47 am

    #66612

    Hi all,

      I want to know if there is any method through which we can sort the data in customized order. If there is any way then pls tell me.

    Any help will be appreciated.

  • June 13, 2005 at 2:18 am

    #564924

    Yes , By using "Case When else end " u can do it

    for example   order by case when type = 'others' then 'zzzz' else type end .

    Here order by is working on field  "type" .

  • June 13, 2005 at 7:28 am

    #564968

    Amit's trick will certainly help you someday. But what do you mean by customized sort? What do you need exactly?

  • June 14, 2005 at 4:50 am

    #565218

    It is really a great idea. But What I need is different. Suppose I have records like:

    B

    A

    F

    D

    E

    C

    I dont want to sort them in ascending or descending order. But in the order I want suppose I want them in order of :

    B

    F

    E

    C

    D

    A

    Then What should I do?

    Thanks in advance

  • June 14, 2005 at 5:02 am

    #565223

    and what order would you call that?

    If you can't mathematically explain an order you can't do it via a set method. The rules you perscribe aren't logical, they're probably a business requirement (at a guess) and therefore you'll probably have to order your records via you application code or use a cursor and iterate inserting into a temporary table or table variable which has a sequential identifier (identity column) which you can then select from and order by, or you create multiple insert statement based on your order

    IE:

    insert into @mytable(col1,col2,etc...)

    select col1,col2,etc... from table1 where myrule='B'

    followed by an insert statement for each of the remaining ordering rules. Then select from the table ordering by your identity column.

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

     

     

     

  • June 14, 2005 at 6:44 am

    #565257

    Another option would be to have another look up table on which you could join to, and then sort on a field of this table (which would be ordered in the same fashion you showed us).

  • June 15, 2005 at 2:44 am

    #565578

    Thanks Enthusiast,

    I was really looking at the query like that. It really works.

  • June 15, 2005 at 8:20 am

    #565715

    BTW his name is Joe Celko, enthusiast is just related to his number of posts.

    Just wanna add that the the longer the string in the charindex function, the slower this will run. Make sure it's not too long for nothing.

  • June 15, 2005 at 9:03 am

    #565731

    Enthusiastic may be way to mild to describe Joe when you hit one of his hot buttons. He does a great rant .

    Mike

    His books are great sources of information. (Just a quick plug for Joe's books) 

     

  • June 15, 2005 at 9:11 am

    #565739

    Never thaught of that one .

Viewing 10 posts - 1 through 9 (of 9 total)

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