Incorrect results if no TOP clause

I have a problem with a query. When I omit the TOP clause I get more
rows returned than I expect (12 rows out of a 119 in the table), but
when I include TOP I get 5 rows from the same data with the otherwise
unchanged table.
Thing is, the query where I include TOP 1000 is the correct result.
I'm on SQL Server 2000, select @.@.version -
"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
I've cut the query down quite a bit, the table
create table Test (
CDMA_PDSN63 FLOAT,
AVE__CORE_46_CountINTEGER,
AVE__CORE_46_Sum FLOAT);
The query is
SELECT * FROM (
SELECT
-- TOP 1000 -- comment this in to get correct results
D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
FROM (SELECT DISTINCT CDMA_PDSN63
FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
) AS D,
(SELECT CDMA_PDSN63,
CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
FROM Test GROUP BY CDMA_PDSN63) AS NM
WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
) as MainQuery
WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
--ORDER BY CDMA_PDSN63 - Note 2
Note 1: I tried adding this but it makes not difference
Note 2: this was in the original query, it makes sense to have it but
again it makes no difference, I've used in on the inner and outer
select, no change
I've also tried TOP 1000 in the outer select again no difference.
Changing the outer join to a regular join fixes the problem, but I
need the outer join in the original query. (In the original query
there are many more sub-selects and several more outer join clauses to
put them back together.)
If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
even stranger, I get a subset of the expected rows, and not the same
number as n.
I tried setting a rowcount as well, no difference.
I've seen several other queries on this newsgroup which talk about
similar problems but the threads have never been concluded with a
clear answer.
Any ideas? Thanks
allan
> "Microsoft SQL Server 2000 - 8.00.194
You're on RTM! Install Service Pack 3a, right away, please! There are many
query processor bugs that have been fixed since the product was released.
Thanks for the CREATE TABLE, but once you've done that, you're going to have
to provide us with enough sample data to reproduce your problem (as well as
tell us which rows you were expecting in the result set!). Otherwise, it's
impossible for us to determine exactly what's happening, why it doesn't meet
your criteria, and test our suggestions on fixing it...
See http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
|||Allan,
After you install service pack 3a as Aaron suggested, I suggest you
consider two other things:
* Rwrite the query using ANSI outer join syntax (... left outer join on
...), which, unlike *=, is always unambiguous
* Consider a different data type than FLOAT for data that must be
compared with the = operator. Because FLOAT values are approximate, you
cannot count on tests of equality to be reliable.
Steve Kass
Drew University
Allan Kelly wrote:

>I have a problem with a query. When I omit the TOP clause I get more
>rows returned than I expect (12 rows out of a 119 in the table), but
>when I include TOP I get 5 rows from the same data with the otherwise
>unchanged table.
>Thing is, the query where I include TOP 1000 is the correct result.
>I'm on SQL Server 2000, select @.@.version -
>"Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
>00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer
>Edition on Windows NT 5.1 (Build 2600: Service Pack 1) "
>I've cut the query down quite a bit, the table
>create table Test (
>CDMA_PDSN63 FLOAT,
>AVE__CORE_46_CountINTEGER,
>AVE__CORE_46_Sum FLOAT);
>The query is
>SELECT * FROM (
>SELECT
>-- TOP 1000 -- comment this in to get correct results
>D.CDMA_PDSN63, NM.AVE__CORE_46x AVE__CORE_46
>FROM (SELECT DISTINCT CDMA_PDSN63
>FROM(SELECT CDMA_PDSN63 FROM Test ) AS dims
>) AS D,
>(SELECT CDMA_PDSN63,
>CASE when (Sum( AVE__CORE_46_Count))=0 then NULL else
>SUM(AVE__CORE_46_Sum) / SUM( AVE__CORE_46_Count) end AS AVE__CORE_46x
>FROM Test GROUP BY CDMA_PDSN63) AS NM
>WHERE D.CDMA_PDSN63*=NM.CDMA_PDSN63
>) as MainQuery
>WHERE ROUND(MainQuery.AVE__CORE_46,0) >= 120
>--AND MainQuery.AVE__CORE_46 IS NOT NULL - Note 1
>--ORDER BY CDMA_PDSN63 - Note 2
>Note 1: I tried adding this but it makes not difference
>Note 2: this was in the original query, it makes sense to have it but
>again it makes no difference, I've used in on the inner and outer
>select, no change
>I've also tried TOP 1000 in the outer select again no difference.
>Changing the outer join to a regular join fixes the problem, but I
>need the outer join in the original query. (In the original query
>there are many more sub-selects and several more outer join clauses to
>put them back together.)
>If I use small values for TOP n (e.g. 1, 2, ... < 10) the results are
>even stranger, I get a subset of the expected rows, and not the same
>number as n.
>I tried setting a rowcount as well, no difference.
>I've seen several other queries on this newsgroup which talk about
>similar problems but the threads have never been concluded with a
>clear answer.
>Any ideas? Thanks
>allan
>
|||Steve Kass <skass@.drew.edu> wrote in message news:<#Tj3pA0dEHA.1604@.TK2MSFTNGP11.phx.gbl>...
> Allan,
> After you install service pack 3a as Aaron suggested, I suggest you
> consider two other things:
> * Rwrite the query using ANSI outer join syntax (... left outer join on
> ...), which, unlike *=, is always unambiguous
> * Consider a different data type than FLOAT for data that must be
> compared with the = operator. Because FLOAT values are approximate, you
> cannot count on tests of equality to be reliable.
>
Thanks Aaron, Steve,
I rewrote the SQL using ANSI outer join syntax and that fixes the
problem. Great!
Thanks for the reminder to look into the service pack, my code need to
run against MSDE in the final product so I need to check out the
service pack situation there.
allan