Skip navigation
bruttas
Currently Being Moderated

order by problem

Feb 18, 2012 8:48 PM

Tags: #by #order

Hi all,

I need help with an order by issue.

 

I query my data for a list of images and I tell it order by name.

It find 11 images for our example and it comes out like this.....

 

CAR-1.jpg

CAR-10.jpg

CAR-11.jpg

CAR-2.jpg

CAR-3.jpg

CAR-4.jpg

CAR-5.jpg

CAR-6.jpg

CAR-7.jpg

CAR-8.jpg

CAR-9.jpg

 

I need it to be arranged like this...

 

CAR-1.jpg

CAR-2.jpg

CAR-3.jpg

CAR-4.jpg

CAR-5.jpg

CAR-6.jpg

CAR-7.jpg

CAR-8.jpg

CAR-9.jpg

CAR-10.jpg

CAR-11.jpg

 

As always thanks for any help.

Brian

 
Replies
  • Currently Being Moderated
    Feb 19, 2012 12:57 AM   in reply to bruttas

    G'day

    When asking a DB question, it helps answer it if you tell us what DB you're using.

     

    You're gonna need to somehow create a column that has only the numeric part of that string, and order by that.

     

    This'll involve using whatever string functions your DB offers to to a find / substr or a regex substitution.  The reason I am vague here is because this is where it's important for you to identify which DB you're using before people will be able to answer your questions thoroughly.

     

    I suspect this will not be a very performant operation, so rearranging your table structure to store that value when the row is inserted / updating might be a better approach, if performance is a concern.

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 19, 2012 5:19 AM   in reply to bruttas

    Right.  All the docs are online, so you can look 'em up yerself.  There'll be a section on string functions... you need to find T-SQL's equivalent of find(), substr() / mid().  SQL Server's regex support is a bit "minimal" from memory (I think perhaps in the latest version it catches up to everything else a bit), so you'll need to use find() to find the "-", and the ".", and the substr() / mid() function to extract the number in between.  Then chuck the result in an INT column, and do a ORDER BY on that.

     

    Make sense?

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 19, 2012 5:47 AM   in reply to bruttas

    You could do something similar to this:

     

    select quality, owner, name

    from imageTBL

    where len(name) = 9

    order by name

     

    union

     

    select quality, owner, name

    from imageTBL

    where len(name) = 10

    order by name

     

    Mind you, I am making the assumption that the database engine will respect the order of the 2 result sets. Some might, others might not.

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 19, 2012 7:59 AM   in reply to bruttas

    In sql server, charindex() will give you the positions of the hyphens and periods.  Then you use substring() to get the part in between, and cast() to convert that substring to the number you want to use in your order by clause.

     

    As Adam said, all your reference material is available on the internet.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points