ORDER BY non-sequential number sequence

  • I have a table where a certain column contains a status id as an integer. I want to sort on this field, but I want to sort in a specific order. Ie, instead of 1,2,3,4,5 I want the order returning as 3,2,4,5,1. That is, all records with statusid 3 first, then 2, etc.

    The only way I can think of is something like this:

    SELECT ... FROM ... WHERE ... ORDER BY CASE WHEN statusId = 3 THEN 0 WHEN stautsId = 2 THEN 1 ... ETC ...

    But I'm wondering if there's another way thats more readable that I've overlooked. I really don't want to do multiple DB hits unless its going to be quicker (queries done via php).

  • Another way...

    ORDER BY CHARINDEX(statusId,'32451')

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Thanks, that looks great. However, the number can become 2 decimal places, so I thought something like:

    select ('S'+CAST ([statusId] AS VARCHAR(255))) as sid from tbl_xenix

    order by CHARINDEX(sid,'S5S4')

    but it says 'sid' is an invalid column, despite the fact its in the column header if I miss out the 'order by' !

    Any clues?

  • select x.NewSid, sid

    from tbl_xenix

    CROSS APPLY (SELECT NewSid = ('S'+CAST ([statusId] AS VARCHAR(255)))) x

    order by CHARINDEX(x.NewSid,'S5S4')


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • You should do it another way. When normalizing the database there should be a table containg all available statusids. Your data table should then have a foreignn key to the statusid-table. When doing so you can add a column to the statusid-table where you define the sort order.

    Something like this:

    create table dbo.domStatus(

    StatusId int not null,

    SortOrder int not null

    primary key ( StatusId )

    );

    create table dbo.SomeDataTable(

    Col1 varchar(50),

    Col2 varchar(50),

    StatusId int not null

    );

    alter table dbo.SomeDataTable add constraint fk_SomeDataTable_domStatus

    foreign key ( StatusId ) references dbo.domStatus ( StatusId );

    select ...

    from dbo.SomeDataTable d inner join

    dbo.domStatus s on

    s.StatusId = d.StatusId

    ORDER BY s.SortOrder

  • Thanks, I actually do have such a table (it translates status id to name and description), so its pretty easy to just add another column with the sort order. Thanks for that, it completely slipped my mind to do it like that.

    Actually, I have several web pages, and currently the list of ids for each page is in the PHP file. I think I'll move it into the DB, it'll make it easier to change.

    I wish there was a way of thanking/voting users on this forum.

  • steve 50602 (1/13/2013)


    Thanks, I actually do have such a table (it translates status id to name and description), so its pretty easy to just add another column with the sort order. Thanks for that, it completely slipped my mind to do it like that.

    Actually, I have several web pages, and currently the list of ids for each page is in the PHP file. I think I'll move it into the DB, it'll make it easier to change.

    I wish there was a way of thanking/voting users on this forum.

    I wish there was a way of thanking/voting users on this forum. << You just did.

  • WolfgangE (1/13/2013)


    You should do it another way. When normalizing the database there should be a table containg all available statusids. Your data table should then have a foreignn key to the statusid-table. When doing so you can add a column to the statusid-table where you define the sort order.

    Something like this:

    create table dbo.domStatus(

    StatusId int not null,

    SortOrder int not null

    primary key ( StatusId )

    );

    create table dbo.SomeDataTable(

    Col1 varchar(50),

    Col2 varchar(50),

    StatusId int not null

    );

    alter table dbo.SomeDataTable add constraint fk_SomeDataTable_domStatus

    foreign key ( StatusId ) references dbo.domStatus ( StatusId );

    select ...

    from dbo.SomeDataTable d inner join

    dbo.domStatus s on

    s.StatusId = d.StatusId

    ORDER BY s.SortOrder

    While that may be a good idea for any incredibly simple system, what do you do when you want to order things differently?

    Add more columns to the domStatus table? No thanks.

    What about when you want the end user to choose a sort order? Add all possible combinations to your domStatus table? No thanks.

    There are times when IMHO you can take "Normalisation" too far and this may be one of them, don't you think? Unless you can definitely say that anything with a status will always need to be ORDERED exactly the same way, I would steer clear of putting that sort of metadata into the database design.

    If you use something simple like the CHARINDEX example, you can provide flexibility at very little cost to design.

    But this is just my opinion and I do appreciate the alternative view put forward by Wolfgang 🙂

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • I completely agree with you, but in this case, the sort order will not be changeable by the user (ever), and there will only ever be one sort order. If the order is ever requested to be changed, then its a simple method of updating a table rather than digging around in the PHP code.

    If I wanted the sort order changeable via the website, then I would definitely use something like CHARINDEX, as it seems a pretty flexible solution. I'd probably just put the concatenation and CAST inside the CHARINDEX function, rather than using a CROSS APPLY.

  • I cannot agree to that and in my opinion there is just one correct answer to this: it depends!

    What do you call an "incredibly simple system"? How do you call other systems? And how do know all the background about the one requirment of some special sorting?

    The way I have shown works perfekt for some requirements, e.g. sorting the items for cash desk accountings in meaningful way that is standard for all 400 shops.

    On the other hand I have never seen a website where a user can define how single values have to be sorted. I only have seen the possibility of giving a column name for sorting, ascending or descending.

    The disadvantages using the cross-apply-method I see are performance and maintenance:

    If the number of rows of the outer select increases the query will slow down as the apply-operator does the subselect for any outer row.

    And what do you do when you have to change the sort logic or if there is a new statusid? In how many procedures will you have to change your code? Will you even find them all? A simple update of 1 table with a column for sort order seems to be much better.

    But as I said: it depends.

    Giving the user the possibility to choose a column for sorting or even let him decide, which values within a column are sorted as the user wants might be difficult. I wonder if this would even be part of the database procedure at all. On huge systems there might be thougts like "how many execution plan for one query do I want in my procedure cache?" or "will my execution plans be cached and reused?" Maybe it's more efficient doing the query without sorting on database level and sort the data in the frontend-programming.

    Yes, there are many aspects. Depending on the background of the requirement you will have to find an appropriate solution.

  • steve 50602 (1/13/2013)


    If I wanted the sort order changeable via the website, then I would definitely use something like CHARINDEX, as it seems a pretty flexible solution. I'd probably just put the concatenation and CAST inside the CHARINDEX function, rather than using a CROSS APPLY.

    Might be but definitely not from query performance perspective.as it doesnt get any help when you put any function on column

    like WHERE left (column_name,1) = 'L' ORDER BY CHARINDEX(statusId,'32451')

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • It's worth mentioning that having a function like that on the ORDER BY can prevent index use. Depending on the size of the data set and the complexity of the rest of the query, while that function provides flexibility, it could seriously kill performance. A structural solution such as having a sorting column is a fairly standard approach and not (in my, admittedly, not worth anything more than anyone else's, opinion) an example of over-normalization.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • WolfgangE (1/14/2013)


    ....

    The disadvantages using the cross-apply-method I see are performance and maintenance:

    If the number of rows of the outer select increases the query will slow down as the apply-operator does the subselect for any outer row....

    Using a single CROSS APPLY valued to the row (i.e. with no table reference) to replace a single calculation referenced in two or more parts of the same query is quite likely to improve performance. Performance change, a very slight change in either direction, will scale in a linear manner with rowcount. It's not a subselect. It's most likely to show in the plan as a compute scalar.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Here's another alternative you could try:

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

    DROP TABLE #TempTable

    CREATE TABLE #TempTable (

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [FirstName] [nvarchar](50) NULL,

    [LastName] [nvarchar](50) NULL,

    [StatusID] [int] NULL,

    PRIMARY KEY (ID))

    INSERT INTO #TempTable

    SELECT 'George','Washington','1'

    UNION ALL SELECT 'John','Adams','2'

    UNION ALL SELECT 'Thomas','Jefferson','3'

    UNION ALL SELECT 'James','Madison','4'

    UNION ALL SELECT 'James','Monroe','5'

    UNION ALL SELECT 'Andrew','Jackson','6'

    UNION ALL SELECT 'John Quincy','Adams','7'

    --Original order

    SELECT * FROM #TempTable ORDER BY ID

    --Order by this string order

    DECLARE @SortOrder VARCHAR(50)

    SET @SortOrder = '3,2,4,5,1,6,7'

    SELECT

    ID

    ,FirstName

    ,LastName

    ,StatusID

    FROM

    #TempTable AS tt

    INNER JOIN

    dbo.DelimitedSplit8K(@SortOrder,',') AS dsk

    ON tt.StatusID = dsk.Item

    For those who don't have the DelimitedSplit8K function already:

    CREATE FUNCTION [dbo].[DelimitedSplit8K]

    (

    @pString VARCHAR(8000)

    ,@pDelimiter CHAR(1)

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    WITH E1(N)

    AS (

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    ),--10E+1 or 10 rows

    E2(N)

    AS (

    SELECT

    1

    FROM

    E1 a

    ,E1 b

    ),--10E+2 or 100 rows

    E4(N)

    AS (

    SELECT

    1

    FROM

    E2 a

    ,E2 b

    ),--10E+4 or 10,000 rows max

    cteTally(N)

    AS (

    SELECT TOP (ISNULL(DATALENGTH(@pString),0))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E4

    ),

    cteStart(N1)

    AS (

    SELECT

    1

    UNION ALL

    SELECT

    t.N + 1

    FROM

    cteTally t

    WHERE

    SUBSTRING(@pString,t.N,1) = @pDelimiter

    ),

    cteLen(N1,L1)

    AS (

    SELECT

    s.N1

    ,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0) - s.N1,8000)

    FROM

    cteStart s

    )

    SELECT

    ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)

    ,Item = SUBSTRING(@pString,l.N1,l.L1)

    FROM

    cteLen l ;

    GO

     

  • Viewing 14 posts - 1 through 13 (of 13 total)

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