I have a subquery that is returning incorrect results. When I run the subquery by itself it returns the correct values, but when it is run as a subquery it returns a different result set. When I run the following: select * from (select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1) zas order by acct It returns: When I run the subquery alone: select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1 order by acct It returns: Is there something I am missing, or is this really a SQL bug? Thanks The code to create the #temp table is as below: create table #temp (acct integer, patient_code char(1), coverage integer, sub_num integer); begin declare @i integer; set @i = 1; while @i < 5500 loop insert into #temp (acct, patient_code, coverage, sub_num) values (@i,'B',1,2); set @i = @i + 1; end loop; end; insert into #temp (acct, patient_code, coverage, sub_num) values (1623,'B',2,2); insert into #temp (acct, patient_code, coverage, sub_num) values (3025,'B',2,2); insert into #temp (acct, patient_code, coverage, sub_num) values (3029,'B',2,2); This is running on 12.0.1.3152 on Windows 7. |
It seems to be a bug solved in newer versions. With 12.0.1.3324, I can reproduce your results. When using the newest Windows build (12.0.1.3519), the query with the derived table lists the desired results, i.e. both queries return the same result: select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1 order by acct; select * from (select acct, patient_code, sub_num, max(coverage) acoverage, count(*) cnt from #temp group by acct, patient_code, sub_num having count(*) > 1) zas order by acct So I would recommend to use a current 12.0.1 EBF - you seem to be using the (quite old) original GA version 12.0.1.3152. BTW: AFAIK, "zas" is not used as a subquery but as a "derived query" here. Thanks for the term. It originally was happening on a subquery, but this was easier to recreate and I never changed it.
(03 Jan '12, 12:01)
tmjac2
Thanks, works on 12.0.1.3519 (latest release).
(03 Jan '12, 14:42)
tmjac2
Comment Text Removed
|