Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

Top Categories · View All
SQL Server 146
TSQL 76
SSRS 71
SSIS 68
XML 57

Best way to sort below mention condition

Nov 30 2012 12:00AM by Alok Chandra Shahi   

I have on table in which i am taking id in following manner.

1
1.5
1.1
1.5.1
1.1.1
1.1.2
1.1.4
1.1.3
1.1.3.1 and so on.

Here i want to do sorting on basis of this column but length of this column is not fix,on i can say that i don't know whether it will contain 3 decimal point number or 4 or 5. so please suggest me optimum logic to sort this column as integer value.
Note- column is varchar

expected output
1
1.1
1.1.1
1.1.2
1.1.3
1.1.3.1
1.1.4
1.5
1.5.1

Submitted under: Microsoft SQL Server · TSQL ·  ·  · 


Alok Chandra Shahi
70 · 3% · 810

14 Replies

  • If the single numbers are walys only one digit long and the sequences are contained in a varchar column then sql server's order by works as expected.

    Are you saying that a single number could be greater than 9?

    In this case, there is no easy solution.

    Some time ago, I ran into this problem in an end user application written by someone else. Users started inserting things like '5.7.11.8', '1.11.3.23', '12.5.18.7', etc. This caused all sql order by clauses to produce bad results. At the time I wrote a scalar-valued funtion which 'normalized' all these codes to 'xx.yy.dd...'. and modified the order by clauses. It was an effective workaround but is probably not applicable to large amounts of data. For large amounts of data you will probably need to write a clr function to do this efficiently.

    commented on Dec 1 2012 4:46AM
    dishdy
    16 · 10% · 3269
  • order by cast('/' + replace(x,'.','/' ) + '/' as hierarchyid) -- sql 2008 order by parsename(x,1),parsename(x,2),parsename(x,3),parsename(x,4) -- max 4 levels

    commented on Dec 1 2012 5:08AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • Leszek,

    Have you got something clever for ss 2005? :-)

    In any case the hiearchyid in ss 2012 is very clever!

    commented on Dec 2 2012 3:28AM
    dishdy
    16 · 10% · 3269
  • I think with parsename you need to do order by parsename(4),parsename(3),parsename(2),parsename(1).

    Try this:

    select c,parsename(c,1),parsename(c,2),parsename(c,3),parsename(c,4)

    from

    (

    select '1.3.3.5' c union all

    select '2.2.3.4'

    ) t

    order by parsename(c,1),parsename(c,2),parsename(c,3),parsename(c,4)

    commented on Dec 2 2012 3:41AM
    dishdy
    16 · 10% · 3269
  • Well, I think parsename is not usable here, esepecially when you have less than 4 elements.

    commented on Dec 2 2012 5:01AM
    dishdy
    16 · 10% · 3269
  • @dishdy

    Have you got something clever for ss 2005? :-)

    select c from cte
    cross apply
    (
        select right(replicate('0',10) + x.i.value('.', 'varchar(8000)'),10)
        from (select cast(('<p>' + replace(c,'.','</p><p>') + '</p>') as xml).query('.') spXML) d 
        cross apply d.spXML.nodes('//p') x(i)
        for xml path('')
    ) cr(v)
    order by cr.v
    

    parsename(c,1),parsename(c,2),parsename(c,3),parsename(c,4)

    yes, it's wrong

    and fix

    order by reverse(parsename(reverse(c),1)),reverse(parsename(reverse(c),2)),reverse(parsename(reverse(c),3)),reverse(parsename(reverse(c),4))
    
    commented on Dec 2 2012 5:02AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • Yes, this way parsename now correctly 'reads' the numbers. But you still need to pad with zeroes to handle numbers with more than 1 digit. E.g.

    1

    10

    2

    But I very much like your cross apply/xml approach!

    And if I run into a real life situation that needs to handle large amounts of data it will provide good motivation to write a clr function.

    commented on Dec 3 2012 1:28AM
    dishdy
    16 · 10% · 3269
  • But you still need to pad with zeroes to handle numbers with more than 1 digit.

    or convert to int / bigint - order by cast(reverse(parsename(reverse(c),1)) as bigint),cast...

    commented on Dec 3 2012 1:56AM
    Leszek Gniadkowski
    8 · 18% · 5729
  • Thanks for your reply,but what should i do when don't have idea that how much decimal will i get in column. that means. i can have n.n.n.n...... so what is the best way.

    commented on Dec 4 2012 12:57PM
    Alok Chandra Shahi
    70 · 3% · 810
  • If you have SS 2012 then the hierarchyid is the best solution. If you have SS 2008 or SS 2005 then the approach shown by Leszek that uses cross apply + xml is the best approach. If you have SS 2000 you will have to write a scalar-valued function that 'normalizes' the x.y.z... sequence. Let me know how you solved your problem.

    commented on Dec 4 2012 3:02PM
    dishdy
    16 · 10% · 3269
  • you dont have to do anything other than convert/cast to a nvarchar/varchar and order by that field ASC

    commented on Dec 5 2012 9:41AM
    Hermann
    593 · 0% · 62
  • Herman, I think you have misunderstood the problem. The issue here is that in the string x.y.z... any of the numbers can exceed a single digit. For example, you can have 1.1, 1.2 and 1.10. When you do order by as you suggested, it comes out in the order 1.1, 1.10, 1.2 which is not correct. It should come out in the order 1.1, 1.2, 1.10.

    commented on Dec 5 2012 12:19PM
    dishdy
    16 · 10% · 3269
  • dishdy can u suggest me on performance issue if i have more than 10000 row on each transaction that means i can have 10 lakhs or more that data in my table.

    commented on Dec 5 2012 1:01PM
    Alok Chandra Shahi
    70 · 3% · 810
  • Alok, I don't understand your question. What do you mean by 'transaction?. What are these '10 lakhs'? And which version of SQL Server are you using? Also, you have not told me yet if in your problem any of the numbers in x.y.z... can have more that one digit. E.g. 1.20.3 or 10.5.20 or 1.202.6

    commented on Dec 5 2012 1:30PM
    dishdy
    16 · 10% · 3269

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]