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.