5 Replies Latest reply: Feb 19, 2012 7:59 AM by Dan Bracuk RSS

    order by problem

    bruttas

      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

        • 1. Re: order by problem
          Adam Cameron. Community Member

          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

          • 2. Re: order by problem
            bruttas

            Hi Adam,

            Im using Microsoft SQL Server 2005

            • 3. Re: order by problem
              Adam Cameron. Community Member

              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

              • 4. Re: order by problem
                BKBK MVP

                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.

                • 5. Re: order by problem
                  Dan Bracuk Community Member

                  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.