Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: selecting sequence

    how to select alphabetic+numeric order i will explain clearly if i had a table containing data like table name is TH and column name is TH
    Code:
    th2, th7, th10, th1, th9, th8,th3,th6,th5-----
    i was trying to get them in sequence i,e expected o/p is
    Code:
    TH
    th1
    th2
    th3
    th5
    th6
    th7
    th8
    th9
    th10
    how can i get them in order
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Obvious question here is

    Is the apha part (the th) fixed length ?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    sorry i am unable to understand what you mean
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Are you saying that, currently, the output sorts like this . . .

    Code:
    TH
    th1
    th10
    th2
    th3
    th5
    th6
    th7
    th8
    th9
    . . . but you want the output to sort like this?

    Code:
    TH
    th1
    th2
    th3
    th5
    th6
    th7
    th8
    th9
    th10
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    yes right i am using the command
    Code:
    select* from TH order by len(th), th
    the problem is that it is taking by length i,e if the data is like TH00 then it put it before the TH10
    hope i explain clearly
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  6. #6
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by GWilliy View Post
    Obvious question here is

    Is the apha part (the th) fixed length ?
    yes it is fixed TH varchar(20)
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    ORDER BY CAST(SUBSTRING(th, 3, 20) AS INT)
    This will work for your sample data. If it errors then you need more representative data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER BY RIGHT(STUFF(th,1,2,'00000'),5)
    i've always wanted to write a query that had the RIGHT STUFF


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    this seems to be in static can we use static in real time
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What do you mean?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i mean the code seems to be in static but my team-met says this is static and we require dynamic.
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  12. #12
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    i,e static means before the program is executed it will satisfy but in dynamic is during the execution of an SQL application
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Either they are wrong or the sample data you showed us was not sufficiently representative.
    Code:
    RIGHT(STUFF(th,1,PATINDEX('%[0-9]%', th) - 1,'00000'), 5)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    depending on how many rows are returned by this query a persisted calculated column may make sense.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    yes you are correct the data in the table is just a example to know the variation on alphanumeric.
    the code is perfect but if i don't know the data type or data in the table then wt code is to be used. in practicing that i asked this question
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •