#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0

    bug with nested order bys


    I am using a work around to achieve a Limitówith offset effect with sql server 7 taken from :

    http://troels.arvin.dk/db/rdbms/

    Just quickly the following sql which works with mysql:

    PHP Code:
    SELECT columns
    FROM tablename
    ORDER BY key ASC
    LIMIT y
    ,
    Can be achieved in mssql using:
    PHP Code:
    SELECT FROM (
      
    SELECT TOP x FROM (
        
    SELECT TOP x FROM (
          
    SELECT TOP (x+ycolumns
          FROM tablename
          ORDER BY key ASC
        
    ) AS foo ORDER BY key DESC -- ('foo' may be anything)
      ) AS 
    bar                     -- ('bar' may be anything)
    ) AS 
    baz ORDER BY key ASC      -- ('baz' may be anything
    The strategy works well and is very fast - even when dealing with 200,000 plus records.

    I've just hit a problem using mssql 7 when using the workaround. mssql seems to ignore the descending order by when ordering by more than two fields. That is; something like:

    PHP Code:
    select top 45 *
    from (
       
    select top 45
           intPeopleID
    ,         
           
    strFirstName,         
           
    strLastName,
       
    from tblOrgPeople
       order by          
           strFirstName ASC       
    base 
    order by      
        strFirstName  DESC 
    produces the expected result. But with:

    PHP Code:
    select top 45 *
    from (
       
    select top 45
           intPeopleID
    ,         
           
    strFirstName,         
           
    strLastName,
       
    from tblOrgPeople
       order by          
           strFirstName
    strLastName ASC       
    base 
    order by      
        strFirstName
    strLastName  DESC 
    the second descending order by is ignored. The result of the above query is sorted by firstname, lastname asscending.

    Has anyone else come across this problem and know of a workaround or a patch to fix it?

    Any help is greatly appreciated,
    Andy.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    No, you got it wrong. Desc/Asc is only applied to the preceeding column.

    Code:
    order by strFirstName desc, strLastName desc
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0
    heh - oops feeling silly now ..

    Thank you! That's a life saver ..

    Andy.

IMN logo majestic logo threadwatch logo seochat tools logo