October 26th, 2003, 11:54 PM
|
|
Junior Member
|
|
Join Date: Oct 2003
Posts: 2
Time spent in forums: < 1 sec
Reputation 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 :
URL
Just quickly the following sql which works with mysql:
PHP Code:
SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT y,x
Can be achieved in mssql using:
PHP Code:
SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP x * FROM (
SELECT TOP (x+y) columns
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.
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
feeling silly now ..
Linear Mode
