Multi Column Sort

  • jsanborn (4/2/2009)


    Mr. Morris

    No such person has responded to this thread.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Ms. Morris?

  • jsanborn (4/2/2009)


    I'm afraid the coalesce won't work either. It's essentially doing the same thing as the case statement. Comparing values from adjacent columns will not produce a correct result.

    On every test case you've posted so far, the coalesce version that I posted has worked. Please post sample data that makes it not work.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The coalesce function will use the next column value if the first one is null, thereby doing much the same thing accomplished with using case statements as suggested earlier. As I've tried to make clear, the occurrence of nulls is random. Therefore, if you are comparing the forth or fifth column value of one row with the first column value of the next row, you won't get a correct sort.

  • This is a hard problem to solve with SQL, and there is also no guarantee of an unambiguous sort order.

    e.g.

    row col1 col2

    ---- ---- ----

    R1 1.0 2.0

    R2 null 2.5

    R3 2.4 null

    R4 4.0 4.5

    In this case, there's no well-defined order for rows R2 and R3.

    Adding a 3rd column with non-null values in both rows R2 and R3 disambiguates the sort order, but the expression COALESCE(col1, col2, col3) would give the wrong sort order in this case.

    row col1 col2 col3

    ---- ---- ---- ----

    R1 1.0 2.0 2.7

    R2 null 2.5 2.8

    R3 2.4 null 3.2

    R4 4.0 4.5 null

    Sorry - no solution yet, but it is an interesting problem.

    Is it a real-world problem for you?

  • Sir, I will bow to your obviously superior knowledge of all things in all universes. But, the results from the first sample were correct. Here are the results from your second sample:

    create table #T (

    ID int identity primary key,

    Col1 float,

    Col2 float,

    Col3 float,

    Col4 float,

    Col5 float,

    Col6 float,

    Col7 float,

    Col8 float);

    insert into #T (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8)

    select 2.35, 3.01, 3.49, 4.25, 4.79, 5.36, 5.82, 6.31 union all

    select null, 3.59, 4.32, null, 5.21, null, null, 7.12 union all

    select 3.88, null, null, 5.23, 5.41, 6.33, 6.89, 7.99 union all

    select 4.12, 4.66, 5.31, 5.92, null, 6.87, 7.34, null;

    select *

    from #T

    order by

    coalesce(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8),

    coalesce(Col2, Col3, Col4, Col5, Col6, Col7, Col8),

    coalesce(Col3, Col4, Col5, Col6, Col7, Col8),

    coalesce(Col4, Col5, Col6, Col7, Col8),

    coalesce(Col5, Col6, Col7, Col8),

    coalesce(Col6, Col7, Col8),

    coalesce(Col7, Col8),

    col8;

    Results in:

    IDCol1Col2Col3Col4Col5Col6Col7Col8

    12.353.013.494.254.795.365.826.31

    2NULL3.594.32NULL5.21NULLNULL7.12

    33.88NULLNULL5.235.416.336.897.99

    44.124.665.315.92NULL6.877.34NULL

    These columns and rows are in the order and pattern you have specified. There is no mathematically possible way for them to NOT be in it.

    So, provide the sample data, the test case, where that solution DOES NOT work. You have not done so thus far. You've been insulting. You've failed to provide necessary data to the solution of the problem. You've done all that, but you haven't disproved my solution.

    So, disprove the solution. Simple as that. Once you have done so, I will refine the solution and provide one that does work. Simple as that.

    Based on the data you have provided, this solution works in all cases. Thus, since you claim it does not work, there is obviously data that does not fit the patterns and rules you have provided thus far. (Well, either that or you're wrong about it not working. But since you being wrong is impossible, I'm discounting that.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • As a side note, my solution also solves andrewd.smith's samples correctly.

    The only ones it failes on are ones that we've been told are impossible, as per my earlier posts. (3,0,9 and 5,4,3 are the combinations it fails on. We're told those kind of patterns can't happen in this data set.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ID Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8

    1 2.35 3.01 3.49 4.25 4.79 5.36 5.82 6.31

    2 NULL 3.59 4.32 NULL 5.21 NULL NULL 7.12

    3 NULL NULL NULL 5.23 5.41 6.33 6.89 7.99

    4 4.12 4.66 5.31 5.92 NULL 6.87 7.34 NULL

    how about here.

  • declare @u2bobnoxious table (ID int, Col1 int, Col2 int, Col3 int)

    insert into @u2bobnoxious

    select 1, 3, 4, 5 union all

    select 2, 1, 2, 0 union all

    select 3, 0, 0, 7 union all

    select 4, 0, 3, 4 union all

    select 5, 4, 0, 0

    select * from @u2bobnoxious

    select * from @u2bobnoxious

    order by isnull(+case when col1 = 0 then '' else str(col1,10,2) end,'')

    +isnull(case when col2 = 0 then '' else str(col2,10,2) end,'')

    +isnull(case when col3 = 0 then '' else str(col3,10,2) end,'')

    -- continue this for however many columns you have in the table

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Nope. Won't work on that one. That's the equivalent of the 3,null,9 case. Col4 of row 4 is lower than Col3 of row 3. (Col5 and Col4 of the same rows also violate that rule.) You had already unequivocally stated that such could not and would not happen.

    So, what's the actual rule on the data?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • G, he just wants a string. Zeroes = nulls = ''s. Make sure that leading zeroes are accounted for and decimals and numbers larger than 1 char. Of course, he can always say FLOAT now and break my solution.

    I now predict Mr. Sanborn will dramatically reveal some other previously undisclosed requirement that everyone should have just OBVIOUSLY known about. That's a childish game.

    [font="Arial"]Edited to add, then delete, my personal opinions about the OPs manners and upbringing.[/font]

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob Hovious (4/2/2009)


    G, he just wants a string. Zeroes = nulls = ''s. Make sure that leading zeroes are accounted for and decimals and numbers larger than 1 char. Of course, he can always say FLOAT now and break my solution.

    I now predict Mr. Sanborn will dramatically reveal some other previously undisclosed requirement that everyone should have just OBVIOUSLY known about. That's a childish game.

    [font="Arial"]Edited to add, then delete, my personal opinions about the OPs manners and upbringing.[/font]

    Oh, I agree. There will be another change in the rules. That's how this works. But it's keeping me mildly entertained at this point.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Not sure what this is all about, but is this something you would expect?

    SELECT *

    , (SELECT COUNT(*) FROM b

    WHERE ISNULL(col1,0) <= ISNULL(al.col1,9)

    AND ISNULL(col2,0) <= ISNULL(al.col2,9)

    AND ISNULL(col3,0) <= ISNULL(al.col3,9)

    AND ISNULL(col4,0) <= ISNULL(al.col4,9)

    AND ISNULL(col5,0) <= ISNULL(al.col5,9)

    AND ISNULL(col6,0) <= ISNULL(al.col6,9)

    AND ISNULL(col7,0) <= ISNULL(al.col7,9)

    AND ISNULL(col8,0) <= ISNULL(al.col8,9)) AS Ordr

    FROM b al

    ORDER BY Ordr

    Best Regards,

    Chris Büttner

  • The last sample I posted was not at all equivalent to that other "3, null, 9" case. (If that is what was being referred to.) In that case, sorting the 3 in order caused the 9 in the same row to be out of order in its column. All I did in my previous post was null one more value in the same table. Every value is still incremental top to bottom.

    Just for the record, if I was so condescending and thought I was so much smarter than everyone else, why would I be posting a question in the first place?

  • ... and I don't know who's asking for strings. I never said anything about strings.

Viewing 15 posts - 16 through 30 (of 180 total)

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