ORDER BY changing resulting row count

I have three queries that only differ in their ORDER BY clause but
return three different result sets. In the examples below "Takedown"
is a table, "CommitID" is an int column, and "TakedownDate" is a
datetime column, and neither of these columns have NULL values.
select * from Takedown where CommitID = 1006204
-- this returns 34 rows
select * from Takedown where CommitID = 1006204 order by TakedownDate
-- this returns 33 rows!
select * from Takedown where CommitID = 1006204 order by TakedownDate
desc
-- this returns 4 rows!
I am running these test in Query Analyzer on SQL Server 2000 (8.00.194)
on XP Pro SP2. I have restarted SQL Server, updated statistics, and
even tried to order by the column's ordinal position instead of name
all with the exact same results.
Anyone have any ideas how this could happen or where I might look for
more info?
Thanks.
TedFirst thing you need to do is install SP3. You are still running the
RTM version, which is practically ancient history. Aside from the known
security vulnerabilities you are exposed to there are also some fixes
that might be relevant to your problem.
http://www.microsoft.com/sql/downloads/2000/sp3.asp
Post again if you still experience problems in SP3.
David Portas
SQL Server MVP
--|||Try,
exec sp_updatestats @.resample = 'resample'
AMB
"Ted O'Connor" wrote:

> I have three queries that only differ in their ORDER BY clause but
> return three different result sets. In the examples below "Takedown"
> is a table, "CommitID" is an int column, and "TakedownDate" is a
> datetime column, and neither of these columns have NULL values.
> select * from Takedown where CommitID = 1006204
> -- this returns 34 rows
> select * from Takedown where CommitID = 1006204 order by TakedownDate
> -- this returns 33 rows!
> select * from Takedown where CommitID = 1006204 order by TakedownDate
> desc
> -- this returns 4 rows!
> I am running these test in Query Analyzer on SQL Server 2000 (8.00.194)
> on XP Pro SP2. I have restarted SQL Server, updated statistics, and
> even tried to order by the column's ordinal position instead of name
> all with the exact same results.
> Anyone have any ideas how this could happen or where I might look for
> more info?
> Thanks.
> Ted
>|||If it does not help, try also:
UPDATE STATISTICS table_name WITH FULLSCAN, ALL
AMB
"Alejandro Mesa" wrote:
> Try,
> exec sp_updatestats @.resample = 'resample'
>
> AMB
> "Ted O'Connor" wrote:
>|||Addtional info...
I have found that there is a float column named BaseToLocal and that
when its excluded from the SELECT the result set is always 38 rows
(which is correct). There appears to be a record where the BaseToLocal
float value = -1.#INF and this is throwing everything off. This table
was populated using DTS with a Paradox table as the source.|||I upgraded my service pack and statistics but the results are the same
when run to grid.
However when I run the results to text I get the partial result set and
"[Microsoft][ODBC SQL Server Driver]Numeric value out of range" which I
assume is do to the -1.#INF value.
My question is how could a float column be storing an out of range
value? Should that have failed somehow during the DTS import?sql