problem creating Self Join query - is this the correct approach?

With the following sample data I want to select only the distinct rows
where Max(mainDate) = '1/25/06' for given recID's, and the distinct
rows must contain the max(subDate). Following is the DDL and Sample
queries that I have tried and sample results:
create table tbl2(
recID int,
subDate datetime,
mainDate datetime)
insert into tbl2
select 1, '11/1/05', '1/5/06' union
select 1, '11/5/05', '1/13/06' union
select 1, '11/12/05', '1/25/06' union
select 1, '11/27/05', '1/25/06' union
select 2, '11/7/05', '1/7/06' union
select 2, '11/13/05', '1/12/06' union
select 2, '11/27/05', '1/15/06' union
select 2, '12/1/05', '2/1/06' union
select 3, '11/3/05', '1/7/06' union
select 3, '11/8/05', '1/12/06' union
select 3, '11/17/05', '1/23/06' union
select 3, '12/1/05', '2/1/06' union
select 4, '11/5/05', '1/3/06' union
select 4, '11/9/05', '1/7/06' union
select 4, '11/19/05', '1/14/06' union
select 4, '11/27/05', '1/25/06' union
select 5, '11/5/05', '1/3/06' union
select 5, '11/9/05', '1/7/06' union
select 5, '11/19/05', '1/25/06' union
select 5, '11/27/05', '1/25/06'
----
--
This Sample query yields the desired result except I have several
fields in my actual project that I need to include in the final version of
the query. In this sample query I have an extra field in the subquery that
I
would like to leave out of the subquery. I believe there is a more efficien
t
way to write this query, and the samples that follow are efforts I have
tried, unsuccessfully. I use this sample here to display my desired result.
--Sample 1
select t1.recid, max(t1.subdate) subDate, t1.mainDate from
(SELECT recid, subDate, MAX(mainDate) AS mainDate
FROM tbl2 --ideally I only want recID and Max(mainDate) in this
subquery
GROUP BY recid, subDate
HAVING MAX(mainDate)='1/25/06') t1
Group By t1.recid, t1.maindate
recID subDate mainDate
1 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
This is the desired result. These rows contain mainDate = '1/25/06'
which is the max date for those recID's and also contain the Max subdate
for the given recID's
----
--
Modifying the subquery (by removing the subDate field) yields this
result. I am getting only the recID's with a max(maindate) of '1/25/06'
but they are not distinct rows.
--Sample 2 -- the subqruery
SELECT recid, subDate, MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid, subDate
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-12 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-19 00:00:00.000 2006-01-25 00:00:00.000
1 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
I need to get only the distinct rows that have a Max(mainDate) of
'1/25/06' for given recID's and are the max(subDate) as in the first
sample. Following is the query with a self join which is the kind of query
I
think I would like to use in my project, but can't get to work correctly.
----
--
--this is the desired query I would like to use in my project to yield the
results the same as Sample 1 above, but this sample is not yielding the
desired distinct rows
--Sample 3
select t1.recid, max(t1.subdate) subDate, t1.maindate from tbl2 t1 join
(SELECT recid, MAX(mainDate) AS mainDate
FROM tbl2 GROUP BY recid, subDate
HAVING MAX(mainDate)='1/25/06') t2 on t1.recid = t2.recid
Group By t1.recid, t1.maindate
this query gives me the desired recID's but is including rows with mainDates
that are not Max(mainDate) - not distinct rows like sample1 . How can I
modify this self join query to yield distinct rows with a Max(mainDate) of
'1/25/06' and the Max(subDate) like the same as sample 1?
recID subDate mainDate
4 2005-11-05 00:00:00.000 2006-01-03 00:00:00.000
5 2005-11-05 00:00:00.000 2006-01-03 00:00:00.000
1 2005-11-01 00:00:00.000 2006-01-05 00:00:00.000
4 2005-11-09 00:00:00.000 2006-01-07 00:00:00.000
5 2005-11-09 00:00:00.000 2006-01-07 00:00:00.000
1 2005-11-05 00:00:00.000 2006-01-13 00:00:00.000
4 2005-11-19 00:00:00.000 2006-01-14 00:00:00.000
1 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
Thanks,
RichNote: Sample 2 is supposed to look like the following (sorry bout that)
--Sample 2 -- the subqruery
SELECT recid, MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID mainDate
1 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
1 2006-01-25 00:00:00.000
4 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
"Rich" wrote:

> With the following sample data I want to select only the distinct rows
> where Max(mainDate) = '1/25/06' for given recID's, and the distinct
> rows must contain the max(subDate). Following is the DDL and Sample
> queries that I have tried and sample results:
> create table tbl2(
> recID int,
> subDate datetime,
> mainDate datetime)
> insert into tbl2
> select 1, '11/1/05', '1/5/06' union
> select 1, '11/5/05', '1/13/06' union
> select 1, '11/12/05', '1/25/06' union
> select 1, '11/27/05', '1/25/06' union
> select 2, '11/7/05', '1/7/06' union
> select 2, '11/13/05', '1/12/06' union
> select 2, '11/27/05', '1/15/06' union
> select 2, '12/1/05', '2/1/06' union
> select 3, '11/3/05', '1/7/06' union
> select 3, '11/8/05', '1/12/06' union
> select 3, '11/17/05', '1/23/06' union
> select 3, '12/1/05', '2/1/06' union
> select 4, '11/5/05', '1/3/06' union
> select 4, '11/9/05', '1/7/06' union
> select 4, '11/19/05', '1/14/06' union
> select 4, '11/27/05', '1/25/06' union
> select 5, '11/5/05', '1/3/06' union
> select 5, '11/9/05', '1/7/06' union
> select 5, '11/19/05', '1/25/06' union
> select 5, '11/27/05', '1/25/06'
> ----
--
> This Sample query yields the desired result except I have several
> fields in my actual project that I need to include in the final version of
> the query. In this sample query I have an extra field in the subquery tha
t I
> would like to leave out of the subquery. I believe there is a more effici
ent
> way to write this query, and the samples that follow are efforts I have
> tried, unsuccessfully. I use this sample here to display my desired resul
t.
> --Sample 1
> select t1.recid, max(t1.subdate) subDate, t1.mainDate from
> (SELECT recid, subDate, MAX(mainDate) AS mainDate
> FROM tbl2 --ideally I only want recID and Max(mainDate) in this
> subquery
> GROUP BY recid, subDate
> HAVING MAX(mainDate)='1/25/06') t1
> Group By t1.recid, t1.maindate
> recID subDate mainDate
> 1 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> This is the desired result. These rows contain mainDate = '1/25/06'
> which is the max date for those recID's and also contain the Max subdate
> for the given recID's
> ----
--
> Modifying the subquery (by removing the subDate field) yields this
> result. I am getting only the recID's with a max(maindate) of '1/25/06'
> but they are not distinct rows.
> --Sample 2 -- the subqruery
> SELECT recid, subDate, MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid, subDate
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-12 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-19 00:00:00.000 2006-01-25 00:00:00.000
> 1 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> I need to get only the distinct rows that have a Max(mainDate) of
> '1/25/06' for given recID's and are the max(subDate) as in the first
> sample. Following is the query with a self join which is the kind of quer
y I
> think I would like to use in my project, but can't get to work correctly.
> ----
--
> --this is the desired query I would like to use in my project to yield th
e
> results the same as Sample 1 above, but this sample is not yielding the
> desired distinct rows
> --Sample 3
> select t1.recid, max(t1.subdate) subDate, t1.maindate from tbl2 t1 join
> (SELECT recid, MAX(mainDate) AS mainDate
> FROM tbl2 GROUP BY recid, subDate
> HAVING MAX(mainDate)='1/25/06') t2 on t1.recid = t2.recid
> Group By t1.recid, t1.maindate
> this query gives me the desired recID's but is including rows with mainDat
es
> that are not Max(mainDate) - not distinct rows like sample1 . How can I
> modify this self join query to yield distinct rows with a Max(mainDate) of
> '1/25/06' and the Max(subDate) like the same as sample 1?
> recID subDate mainDate
> 4 2005-11-05 00:00:00.000 2006-01-03 00:00:00.000
> 5 2005-11-05 00:00:00.000 2006-01-03 00:00:00.000
> 1 2005-11-01 00:00:00.000 2006-01-05 00:00:00.000
> 4 2005-11-09 00:00:00.000 2006-01-07 00:00:00.000
> 5 2005-11-09 00:00:00.000 2006-01-07 00:00:00.000
> 1 2005-11-05 00:00:00.000 2006-01-13 00:00:00.000
> 4 2005-11-19 00:00:00.000 2006-01-14 00:00:00.000
> 1 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000
> Thanks,
> Rich
>|||I may have requested a tall order to fill. Here is my revised request. How
can I revised the following query to return a result like the desired result
below vs the actual result (from the data below that)?
SELECT recid, MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid, subDate
HAVING MAX(mainDate)='1/25/06'
--desired result
recID mainDate
1 2006-01-25 00:00:00.000
4 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
--actual result
recID mainDate
1 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
1 2006-01-25 00:00:00.000
4 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
insert into tbl2
select 1, '11/1/05', '1/5/06' union
select 1, '11/5/05', '1/13/06' union
select 1, '11/12/05', '1/25/06' union
select 1, '11/27/05', '1/25/06' union
select 2, '11/7/05', '1/7/06' union
select 2, '11/13/05', '1/12/06' union
select 2, '11/27/05', '1/15/06' union
select 2, '12/1/05', '2/1/06' union
select 3, '11/3/05', '1/7/06' union
select 3, '11/8/05', '1/12/06' union
select 3, '11/17/05', '1/23/06' union
select 3, '12/1/05', '2/1/06' union
select 4, '11/5/05', '1/3/06' union
select 4, '11/9/05', '1/7/06' union
select 4, '11/19/05', '1/14/06' union
select 4, '11/27/05', '1/25/06' union
select 5, '11/5/05', '1/3/06' union
select 5, '11/9/05', '1/7/06' union
select 5, '11/19/05', '1/25/06' union
select 5, '11/27/05', '1/25/06'
Thanks|||Try:
SELECT recid, MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9C611822-D20E-476B-8A82-B841E9F30E6D@.microsoft.com...
I may have requested a tall order to fill. Here is my revised request. How
can I revised the following query to return a result like the desired result
below vs the actual result (from the data below that)?
SELECT recid, MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid, subDate
HAVING MAX(mainDate)='1/25/06'
--desired result
recID mainDate
1 2006-01-25 00:00:00.000
4 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
--actual result
recID mainDate
1 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
1 2006-01-25 00:00:00.000
4 2006-01-25 00:00:00.000
5 2006-01-25 00:00:00.000
insert into tbl2
select 1, '11/1/05', '1/5/06' union
select 1, '11/5/05', '1/13/06' union
select 1, '11/12/05', '1/25/06' union
select 1, '11/27/05', '1/25/06' union
select 2, '11/7/05', '1/7/06' union
select 2, '11/13/05', '1/12/06' union
select 2, '11/27/05', '1/15/06' union
select 2, '12/1/05', '2/1/06' union
select 3, '11/3/05', '1/7/06' union
select 3, '11/8/05', '1/12/06' union
select 3, '11/17/05', '1/23/06' union
select 3, '12/1/05', '2/1/06' union
select 4, '11/5/05', '1/3/06' union
select 4, '11/9/05', '1/7/06' union
select 4, '11/19/05', '1/14/06' union
select 4, '11/27/05', '1/25/06' union
select 5, '11/5/05', '1/3/06' union
select 5, '11/9/05', '1/7/06' union
select 5, '11/19/05', '1/25/06' union
select 5, '11/27/05', '1/25/06'
Thanks