how are these queries evaluted differently by sql server?

I'm looking for the minimum date of an entry into a history table. The table contains multiple entries for the customer and the item with an activation and deactivation date for each entry.

I could use the following:

select customerId, item, min(activationDate) from history group by customerId, item

or a sub query

select customerId, item, activationDate

from history h1

where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)

How are these two queries parsed differently by SQL.

They return a different number of results.

Thanks,

karen

I'm guessing that customerId and item do not uniquely define a record in the table, is this true? If so, that would explain the difference in the amount of rows between the two result sets.

-The first query finds the minimum activationDate for each unique customerId and item.

-The second query finds the minimum activationDate for each customerId and item.

-If you add a group by customerId, item to the second query you should have matching result sets.

The inner query of the correlated subquery is processed once per record of the outer query.

|||

If you have the following data in the table:

CustomerID - Item - ActivationDate

1 1 1/1/2007

1 1 1/1/2007

The first query will return one record while the second query will return two. This is because (as the above post indicates), that the first query is returning a true grouping while the second will return duplicate records if there is duplicate data in the table.

|||

Bcs there are duplicate entries available on your database..

You can force DISTINCT class to fix this..

Sample..

Create Table #samplehistory (

[customerId] int ,

[item] int ,

[activationDate] datetime

);

Insert Into #samplehistory Values('1','1','1/1/2007');

Insert Into #samplehistory Values('1','1','1/2/2006');

Insert Into #samplehistory Values('1','1','1/3/2006');

Insert Into #samplehistory Values('1','2','1/11/2007');

Insert Into #samplehistory Values('1','2','1/11/2003');

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate) activationDate

from

#samplehistory

group by

customerId, item

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

After duplicating one of the value.. You are result is correct but there are dupicate data in the result

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate)

from

#samplehistory

group by

customerId, item

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

122002-01-11 00:00:00.000

*/

The group by class force the First query to avoid the duplicates (already distincted values are return).

After Distinct on second query,

Code Snippet

select distinct

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

|||

Thank you very much for your help. I really appreciate the time.

Would you recommend a book for dealing with these kinds of sublties in SQL?

Karen

|||

Inside SQL Server 2005 T-SQL Querying by Itzik Ben-Gan is very good.

|||Ken Henderson's "The Guru's Guide to Transact-SQL" is also very good.

Hotfix for 826906 in SP4?

Greetings.
Apparently the fix described in article 826906 ("A query that uses a view
that contains a correlated subquery and an aggregate runs slowly") didn't
make it into SP4.
I think I'm getting bitten by this, and don't have an easy way to rewrite
the query (it's generated by Analysis Services). Is there any ETA for a
corresponding hotfix to be released for SP4?
Regards,
JonathanHello,
The final release of this hotfix for SP4 has not been determined. If there
is any update we will let you know.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Sophie,
Thanks for your reply.
"Sophie Guo [MSFT]" wrote:
> The final release of this hotfix for SP4 has not been determined. If there
> is any update we will let you know.
The knowlege base article for 826906 says that the hotfix will be available
"shortly."
This bug is biting us hard. Can you give me a hint as to what "shortly"
means?
Thanks,
Jonathan|||Hello,
I appologize that we are unable to offer any further information on this
matter. Once the update is released, we will update you as soon as
possible and the article will be update.
I suggest that you unsintall SP4 and reinstall SP3 and the hotfix if the
issue is urgent. For more information about uninstalling SP4, you may refer
to the readme file of the SQL server 2000 SP4.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Do these guys even read this stuff?
Johnathan, how do you know whether or not this fix made it into the SP4
release?
As you know, SQL Server builds are cumulative. The fix you are referring to
is build 8.00.850. The cutoff build for the SP4 Beta was originally set at
8.00.972 and relabled 8.00.2026 to allow additional post-SP3 hotfixes not
included in SP4. The final release for SP4, build 8.00.2039, looks like it
included all fixes up through build 8.00.1007.
Also, here is the published list of fixes for the SP4 build:
A list of the bugs that are fixed in SQL Server 2000 Service Pack 4
http://support.microsoft.com/default.aspx?scid=kb;en-us;888799
If you need it, here is the list of post-SP3 hotfixes:
SQL Server 2000 hotfix update for SQL Server 2000 Service Pack 3 and 3a
http://support.microsoft.com/?kbid=810185
Hope this helps.
Sincerely,
Anthony Thomas
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:1%23mFpMfYFHA.3336@.TK2MSFTNGXA01.phx.gbl...
Hello,
I appologize that we are unable to offer any further information on this
matter. Once the update is released, we will update you as soon as
possible and the article will be update.
I suggest that you unsintall SP4 and reinstall SP3 and the hotfix if the
issue is urgent. For more information about uninstalling SP4, you may refer
to the readme file of the SQL server 2000 SP4.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Anthony,
Thanks for your reply.
"Anthony Thomas" wrote:
> Johnathan, how do you know whether or not this fix made it into the SP4
> release?
I'm going by the text of article 826906, which says, in part, "Note This
hotfix is not included with Microsoft SQL Server 2000 Service Pack 4 (SP4).
If you apply this hotfix and then apply SP4, this hotfix will be overwritten.
A hotfix for SP4 will soon be available."
Regards,
Jonathan|||Yep, it sure does say that, but the Fixlist KB shows that it is included;
so, since the KB you are referencing is more recent (by about a week, at
most), I'd have to go with that. However, that is in direct contridiction
with how MS deploys cumulative builds.
The only thing I can conclude from this is that it had been included in the
Beta version but had been removed prior to public release.
So, your choices are to go ahead and deploy SP 4 and wait for the hotfix
they are working on to reinclude it, contact PSS to get the hot fix listed
in this KB, or apply a different, later post-SP3 hotfix that MUST contain
this fix, following MS cumulative build strategy.
The latest publicly available, post-SP3 hotfix is for build 8.00.878 and can
be downloaded here:
You must install the SQL Server 2000 update that KB article 831950 describes
to run BizTalk Server 2004
http://support.microsoft.com/?kbid=838166
For the english version:
http://download.microsoft.com/download/9/0/4/9046652b-2b9b-4d49-98c8-e74ba1849af5/sql2000-kb810185-8.00.0878-enu.exe
This one replaced the last publicly available, post-SP3 hotfix, build
8.00.859 because it had severe bugs in it.
Hope this helps.
Sincerely,
Anthony Thomas
"Jonathan Levine" <myfoo2@.nospam.nospam> wrote in message
news:9ADED83C-EDBE-4811-8881-622AA3C21103@.microsoft.com...
Anthony,
Thanks for your reply.
"Anthony Thomas" wrote:
> Johnathan, how do you know whether or not this fix made it into the SP4
> release?
I'm going by the text of article 826906, which says, in part, "Note This
hotfix is not included with Microsoft SQL Server 2000 Service Pack 4 (SP4).
If you apply this hotfix and then apply SP4, this hotfix will be
overwritten.
A hotfix for SP4 will soon be available."
Regards,
Jonathan|||"Sophie Guo [MSFT]" wrote:
> I appologize that we are unable to offer any further information on this
> matter. Once the update is released, we will update you as soon as
> possible and the article will be update.
Sophie,
It's now been a month since article 826906 was last updated, promising
that a "hotfix for SP4 will soon be available."
Is there any news on when it will be "soon?"
Regards,
Jonathan|||Hello,
The exact ship date is not known as it must await another package being
delivered. Thanks.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, Sophie. Any news on this one? It's been months now...
Regards,
Jonathan
"Sophie Guo [MSFT]" wrote:
> Hello,
> The exact ship date is not known as it must await another package being
> delivered. Thanks.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>|||Hello,
I read the folloiwng information internally:
This hotfix is scheduled to be included with a post-Service Pack 4 hotfix
rollup package and with SQL Server 2000 Service Pack 5.
If the issue is urgent, please contact Customer Service and Support (CSS)
for immediate assistance. For more information on available CSS services,
please click here:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
NOTE: In special cases, charges that are ordinarily incurred for support
calls may be canceled if a Microsoft Support Professional determines that a
specific update will resolve your problem. The typical support costs will
apply to additional support questions and issues that do not qualify for
the specific update in question.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sophie, thanks for your reply.
"Sophie Guo [MSFT]" wrote:
> This hotfix is scheduled to be included with a post-Service Pack 4 hotfix
> rollup package and with SQL Server 2000 Service Pack 5.
> If the issue is urgent, please contact Customer Service and Support (CSS)
> for immediate assistance. For more information on available CSS services,
> please click here:
I'm not sure what this means. Does it mean:
1. There's a hotfix I can get now?
Or
2. The fix is available but hasn't been packaged yet, and won't be
packaged unless enough people call and complain?
Or
3. The fix is not yet available?
Or
4. Something else?
Thanks,|||Hello,
The hotfix for SP4 is still unavailable now. However, if the issue is
urgent, CSS will investigate this issue at the urgent level and might
provide a hot fix for you. Thanks.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.