Problem Getting Distinct Records

I have 2 tables that I'm trying to join. The first table is called DeviationMaster and the second is called DeviationDist
I have no problem returning all results for deviationMaster. My problem is, when I try and join DeviationDist to DeviationMaster I get alot of duplicate records. DeviationDist contains a column named DEVDN which is the Deviation Number column. It also contains a column DEVDD which is the Distributor name. I join them by saying where DNumber (which is the Deviation column in DeviationMaster) is equal to DEVDN in DeviationDist. This works fine when I only select the DEVDN column, but I really need the DEVDD column as well. When I grab the DEVDD column, it then shows duplicate records since there can be multiple DEVDD for each DEVDN in DeviationDist.
I guess I need to know how I can only grab 1 occurance for each DEVDN in DeviationDist and relate that to the DeviationMaster table.
This statement works but does not get DEVDD which is the dist number column that I need to get. This of course doesnt duplicate.
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC
This statement gets the DEVDD column but now I may have multiple Deviations present depending on how many times DEVDD repeats for each DEVDN. I need to grab only 1 of each DEVDD and DEVDN.
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDD, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC

I'm stuck, any help is appreciated.

Hello!
Is guess this is a small controversy within SQL - but you should review the DISTINCT versus the GROUP BY - there is a lot of different opinoins there but from my humble position within this coding world
I always use the Group By and I always get what I want....
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
GROUP BY DD.DEVDN
ORDER BY dm.DNumber DESC|||Sorry --
Please remove Select Distinct and Replace with just a Select
Best Regards,
Joe|||

This could be a solution, i think:
SELECT max(dm.DNumber), max(dm.TNumber), max(dm.Status), max(dm.DType), dd.DEVDD, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC
GROUP by dd.DEVDD, dd.DEVDN
but this would make DeviationDist the Mastertable and DeviationMaster the DetailsTable since it would only by used for aggregates.
This way you would get a summary of DEVDD's ans DEVDN's with (in this case) the maxima of DNumber, TNumber, ...
Hope this helps

|||

I tried

SELECT dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
GROUP BY DD.DEVDN
ORDER BY dm.DNumber DESC
I get an error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.DNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.TNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.DType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||This still produced multiple records.|||You could do something like this :
SELECT dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
AND dd.DEVDN IN ( SELECT dd.DEVDN FROM DeviationMaster dm INNER JOIN DeviationDist dd ON dm.DNumber = dd.DEVDN GROUP BY DD.DEVDN)
ORDER BY dm.DNumber DESC
|||

I still need to grab column dd.DEVDD...

Also, these are still returning 35xxxx records and the DeviationMaster only has 8xxx records. I didnt know it wa sso complicated to only grab the first dist is sees for each DEVDN and DNumber...

|||

Lets say for DeviationDist there is DEVDD and DEVDN

If I say select distinct DEVDN, it returns 8xxx records which is good. I need it to do the same when DEVDD is added to the mix but it goes right to 35xxx records if I do distinct devdd, devdn...
Isnt there a way to say for each devdn, get the devdd but ignore the rest?

|||can you provide some sample data and the kind of resultset that you are expecting back. Also is DevDN the primarykey in the DevDD table ?|||

DeviationDist table has a DEVID Column which is just numeric identity, pk... DEVDN which is Deviation Number and DEVDD which is distributor.

I need only one occurance of the DEVDD per each DEVDN and then I need to join that to the DeviationMaster table on column DNumber which is the deviation number.

Lets say this is the Table:

DEVID DEVDN DEVDD
1 100000 0920292
2 100000 2920292
3 100000 2928373
4 100001 9383939
5 100001 4484949
6 100002 5959595
7 100003 3939393

When I run the query, I only want to see records 1, 4, 6, 7. Since 2, 3 are just 2 more distributors for the same deviation (DEVDN) and same as 5. That way, when I link to the DeviationMaster, I will get a one to one result. When I do Distinct like I said above, it still returns all these but I only want 1 record for each DEVDN.

|||

check this :
SELECT
dd.devdn, min(dd.devdd)
,dm.dnumber, dm.tnumber, dm.status, dm.dtype
from
Devdn DD
INNER JOIN DeviationMaster DM ON dm.dnumber = dd.DevId
GROUP BY
dd.devdn,
dm.dnumber, dm.tnumber, dm.status, dm.dtype

|||

That returned no results:

SELECT dd.devdn, min(dd.devdd),dm.dnumber, dm.tnumber, dm.status, dm.dtype
FROM DeviationDist DD
INNER JOIN DeviationMaster DM ON dm.dnumber = dd.DevId
GROUP BY dd.devdn, dm.dnumber, dm.tnumber, dm.status, dm.dtype

|||

here's what I tried :

declare @.dm table ( dnumber int, tnumber int, status varchar(10), dtype varchar(10) )
declare @.dd table (pk int, devdn int, devdd varchar(10) )

insert into @.dm values (1,133,'a', 'a')
insert into @.dm values (2,243,'a', 'a')
insert into @.dm values (3,353,'a', 'a')
insert into @.dm values (4,483,'a', 'a')
insert into @.dm values (5,563,'a', 'a')
insert into @.dm values (6,663,'a', 'a')
insert into @.dm values (7,763,'a', 'a')

insert into @.dd values ( 1 , 100000,1920292)
insert into @.dd values ( 2,100000,2920292)
insert into @.dd values ( 3,100000, 2928373)
insert into @.dd values ( 4,100001, 9383939)
insert into @.dd values ( 5, 100001, 4484949)
insert into @.dd values ( 6,100002, 5959595)
insert into @.dd values ( 7, 100003, 3939393)

Select min(ddd.pk),devdn from @.dd ddd group by ddd.devdn

--the above will give you distinct combination of devdn and devdd
--use the above to join to deviation master
select
min(dd.devdn), min(dd.devdd)
,dm.dnumber, dm.tnumber, dm.status, dm.dtype
from
@.dd dd
inner join @.dm dm on dm.dnumber = dd.pk
and
dd.pk in ( Select min(ddd.pk) from @.dd ddd group by ddd.devdn)
group by dd.devdn,
dm.dnumber, dm.tnumber, dm.status, dm.dtype

Here's the resultset:
100000 1920292 1 133 a a
100001 9383939 4 483 a a
100002 5959595 6 663 a a
100003 3939393 7 763 a a

|||I'm not sure why yours works and mine did nothing. Does it matter that DeviationMaster has no PK and that DEVDN is varchar, not integer?
DNumber should be numeric, TNumber is varchar...
I need exactly the result you got, ahh!

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

problem creating Self Join query - is this the correct approac

Thank you for your response. Actually, your suggestion was the original
subquery I was using which did yield distinct rows. I have not been able to
duplicate my actual problem. The actual problme is that I have to select
several fields per row from this one table with the criteria that each recID
has to be unique and has to be the Max(maindate) for a given date and has to
be the max(subdate) for the group of rows per recID containing the
Max(mainDate). Heck, let me start the request over because I got all messed
up.
Actually, I think I found the answer I was looking for (I had to change my
data to give me different Max(subDates):
SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 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
Where I was messing up was trying to include Max(subDate) in the Having
clause. Now I got it straight. Now I can create a self join from this
table to the main table and get the correct rows with the additional fields
that I need that meet this criteria. Before I was trying to use Max(subDate
)
from the main table but I had to add a Group By Clause and include all of th
e
additional fields in the Group By clause which added rows. Hopefully, this
will work.
Thank you for your reply. It motivated me.
RichLooks like you're on the right. Might I suggest a cup of caffeine? Works
for me. ;-)
Just a note of caution about your query. It will give the max(subDate),
even if that subDate does not correspond to a row that has a mainDate =
2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
2006-01-25 and then s out the max (subDate) corresponding to those rows.
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:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
Thank you for your response. Actually, your suggestion was the original
subquery I was using which did yield distinct rows. I have not been able to
duplicate my actual problem. The actual problme is that I have to select
several fields per row from this one table with the criteria that each recID
has to be unique and has to be the Max(maindate) for a given date and has to
be the max(subdate) for the group of rows per recID containing the
Max(mainDate). Heck, let me start the request over because I got all messed
up.
Actually, I think I found the answer I was looking for (I had to change my
data to give me different Max(subDates):
SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
FROM tbl2
GROUP BY recid
HAVING MAX(mainDate)='1/25/06'
recID subDate mainDate
1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
4 2005-11-23 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
Where I was messing up was trying to include Max(subDate) in the Having
clause. Now I got it straight. Now I can create a self join from this
table to the main table and get the correct rows with the additional fields
that I need that meet this criteria. Before I was trying to use
Max(subDate)
from the main table but I had to add a Group By Clause and include all of
the
additional fields in the Group By clause which added rows. Hopefully, this
will work.
Thank you for your reply. It motivated me.
Rich|||Thank you for your suggestion. That is a great idea. As for the caffein,
that would also be a great idea except that (I am a general aviation pilot)
I
have a flight physical this Friday - gotta make sure the BP is nice a low fo
r
the nice doctor :).
Thank you for your help.
"Tom Moreau" wrote:

> Looks like you're on the right. Might I suggest a cup of caffeine? Works
> for me. ;-)
> Just a note of caution about your query. It will give the max(subDate),
> even if that subDate does not correspond to a row that has a mainDate =
> 2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
> 2006-01-25 and then s out the max (subDate) corresponding to those rows
.
> --
> 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:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
> Thank you for your response. Actually, your suggestion was the original
> subquery I was using which did yield distinct rows. I have not been able
to
> duplicate my actual problem. The actual problme is that I have to select
> several fields per row from this one table with the criteria that each rec
ID
> has to be unique and has to be the Max(maindate) for a given date and has
to
> be the max(subdate) for the group of rows per recID containing the
> Max(mainDate). Heck, let me start the request over because I got all mess
ed
> up.
> Actually, I think I found the answer I was looking for (I had to change my
> data to give me different Max(subDates):
> SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 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
> Where I was messing up was trying to include Max(subDate) in the Having
> clause. Now I got it straight. Now I can create a self join from this
> table to the main table and get the correct rows with the additional field
s
> that I need that meet this criteria. Before I was trying to use
> Max(subDate)
> from the main table but I had to add a Group By Clause and include all of
> the
> additional fields in the Group By clause which added rows. Hopefully, thi
s
> will work.
> Thank you for your reply. It motivated me.
> Rich
>|||I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
month. I used to stay away from coffee on the same day as my medical. Now,
I don't. I've been getting very good BP and resting pulse rates in spite of
it.
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:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
Thank you for your suggestion. That is a great idea. As for the caffein,
that would also be a great idea except that (I am a general aviation pilot)
I
have a flight physical this Friday - gotta make sure the BP is nice a low
for
the nice doctor :).
Thank you for your help.
"Tom Moreau" wrote:

> Looks like you're on the right. Might I suggest a cup of caffeine? Works
> for me. ;-)
> Just a note of caution about your query. It will give the max(subDate),
> even if that subDate does not correspond to a row that has a mainDate =
> 2006-01-25. Perhaps you need to get recID's that have a max(subDate) =
> 2006-01-25 and then s out the max (subDate) corresponding to those
> rows.
> --
> 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:D32123C7-A2BC-4766-8B27-521958DA99C3@.microsoft.com...
> Thank you for your response. Actually, your suggestion was the original
> subquery I was using which did yield distinct rows. I have not been able
> to
> duplicate my actual problem. The actual problme is that I have to select
> several fields per row from this one table with the criteria that each
> recID
> has to be unique and has to be the Max(maindate) for a given date and has
> to
> be the max(subdate) for the group of rows per recID containing the
> Max(mainDate). Heck, let me start the request over because I got all
> messed
> up.
> Actually, I think I found the answer I was looking for (I had to change my
> data to give me different Max(subDates):
> SELECT recid, Max(subDate), MAX(mainDate) AS mainDate
> FROM tbl2
> GROUP BY recid
> HAVING MAX(mainDate)='1/25/06'
> recID subDate mainDate
> 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000
> 4 2005-11-23 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
> Where I was messing up was trying to include Max(subDate) in the Having
> clause. Now I got it straight. Now I can create a self join from this
> table to the main table and get the correct rows with the additional
> fields
> that I need that meet this criteria. Before I was trying to use
> Max(subDate)
> from the main table but I had to add a Group By Clause and include all of
> the
> additional fields in the Group By clause which added rows. Hopefully,
> this
> will work.
> Thank you for your reply. It motivated me.
> Rich
>|||This reply is in lieu of the caffeine :). I did the whole route in aviation
,
CFI, miliatary, commercial (part 135). I wasn't really making much headway
after a while, so I threw the towel in on commercial aviation and went for D
B
programming (EE major in college). I just stay current for part 91 flying.
But having the sitdown 8 hrs a day job, drinking caffeine for the last bunch
of years, has taken its toll on me. Now I have to keep an eye on the BP. S
o
I ride my bicycle to the workplace.
As for the project I am working on, man, the data is like an ocean. Nothing
gets deleted, just updated, added, and moved around to various tables. The
architecture is OK, normalizatin OK (data gets replicated - that's OK). But
there are so many twists that I have to compensate for, so I have to write
these crazy queries. Even so, this is still working out a little better
than the aviation scene for me.
--Rich
"Tom Moreau" wrote:

> I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
> month. I used to stay away from coffee on the same day as my medical. No
w,
> I don't. I've been getting very good BP and resting pulse rates in spite
of
> it.
> --
> 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:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
> Thank you for your suggestion. That is a great idea. As for the caffein,
> that would also be a great idea except that (I am a general aviation pilot
)
> I
> have a flight physical this Friday - gotta make sure the BP is nice a low
> for
> the nice doctor :).
> Thank you for your help.
> "Tom Moreau" wrote:
>
>|||I started life as a scientist. One of my first discoveries was that there's
no money in scientific research, so I made a "lateral" move to IT. I had a
lot of computing background while an undergrad and as a grad. It's amazing
how many people are in IT that didn't start there.
Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
I'm inclined. ;-)
I hear ya about the data. I had an assignment with a police database. They
delete nothing - they just buy a bigger server!
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:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
This reply is in lieu of the caffeine :). I did the whole route in
aviation,
CFI, miliatary, commercial (part 135). I wasn't really making much headway
after a while, so I threw the towel in on commercial aviation and went for
DB
programming (EE major in college). I just stay current for part 91 flying.
But having the sitdown 8 hrs a day job, drinking caffeine for the last bunch
of years, has taken its toll on me. Now I have to keep an eye on the BP.
So
I ride my bicycle to the workplace.
As for the project I am working on, man, the data is like an ocean. Nothing
gets deleted, just updated, added, and moved around to various tables. The
architecture is OK, normalizatin OK (data gets replicated - that's OK). But
there are so many twists that I have to compensate for, so I have to write
these crazy queries. Even so, this is still working out a little better
than the aviation scene for me.
--Rich
"Tom Moreau" wrote:

> I, too, am a GA pilot. (I own a Grumman Cheetah.) My medical is next
> month. I used to stay away from coffee on the same day as my medical.
> Now,
> I don't. I've been getting very good BP and resting pulse rates in spite
> of
> it.
> --
> 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:793EB8C8-7E9D-4AE3-A240-5D677371A2EC@.microsoft.com...
> Thank you for your suggestion. That is a great idea. As for the caffein,
> that would also be a great idea except that (I am a general aviation
> pilot)
> I
> have a flight physical this Friday - gotta make sure the BP is nice a low
> for
> the nice doctor :).
> Thank you for your help.
> "Tom Moreau" wrote:
>
>|||I just have to ask this one last question as far as scientists, go - incase
you have not seen a movie that was released just this month called "8 Below*
about 8 sled dogs in Antarctica, their master, and a "scientist" from l thin
k
UCLA in the movie - great movie, but they really make the scientist guy look
pretty happening - had the nice house in LA somewhere, big grant from the
University. I take it this only happens in Hollywood?:).
BTW, it was a great movie, especially if you are a dog person. Oh, and to
keep this on Topic, I have to get a count of items for my current project.
This would seems uneventful except for the part that no one has been able to
get accurate/consistent counts. The last guy to try was a young wiz kid, bu
t
no go. So I am tearing apart the DB on a Dev server and finding a variety o
f
data discrepancies like ID's in history tables pertaining to given entities
and the same entities in the active tables with different ID's. I have
already been instructed to Update the history tables to the Active ID's.
Thus, my crazy queries. This data has been getting collected for over 17
years! I'm just going to John Henry it (sledge hammer my way through the
data).
--Rich
"Tom Moreau" wrote:

> I started life as a scientist. One of my first discoveries was that there
's
> no money in scientific research, so I made a "lateral" move to IT. I had
a
> lot of computing background while an undergrad and as a grad. It's amazi
ng
> how many people are in IT that didn't start there.
> Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
> I'm inclined. ;-)
> I hear ya about the data. I had an assignment with a police database. Th
ey
> delete nothing - they just buy a bigger server!
> --
> 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:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
> This reply is in lieu of the caffeine :). I did the whole route in
> aviation,
> CFI, miliatary, commercial (part 135). I wasn't really making much headwa
y
> after a while, so I threw the towel in on commercial aviation and went for
> DB
> programming (EE major in college). I just stay current for part 91 flying
.
> But having the sitdown 8 hrs a day job, drinking caffeine for the last bun
ch
> of years, has taken its toll on me. Now I have to keep an eye on the BP.
> So
> I ride my bicycle to the workplace.
> As for the project I am working on, man, the data is like an ocean. Nothi
ng
> gets deleted, just updated, added, and moved around to various tables. Th
e
> architecture is OK, normalizatin OK (data gets replicated - that's OK). B
ut
> there are so many twists that I have to compensate for, so I have to write
> these crazy queries. Even so, this is still working out a little better
> than the aviation scene for me.
> --Rich
> "Tom Moreau" wrote:
>
>|||I hear ya. Haven't seen the movie, but a scientist with a nice house and
big grant doesn't sound very credible. My cousin took a voluntary pay cut
so he could get lab equipment. Then, he moved from Canada to Wisconsin and
things improved, though I wouldn't call it lucrative.
Good luck on the data cleansing!
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:02B08CB1-0C9F-4674-8539-A3A5DD3B076B@.microsoft.com...
I just have to ask this one last question as far as scientists, go - incase
you have not seen a movie that was released just this month called "8 Below*
about 8 sled dogs in Antarctica, their master, and a "scientist" from l
think
UCLA in the movie - great movie, but they really make the scientist guy look
pretty happening - had the nice house in LA somewhere, big grant from the
University. I take it this only happens in Hollywood?:).
BTW, it was a great movie, especially if you are a dog person. Oh, and to
keep this on Topic, I have to get a count of items for my current project.
This would seems uneventful except for the part that no one has been able to
get accurate/consistent counts. The last guy to try was a young wiz kid,
but
no go. So I am tearing apart the DB on a Dev server and finding a variety
of
data discrepancies like ID's in history tables pertaining to given entities
and the same entities in the active tables with different ID's. I have
already been instructed to Update the history tables to the Active ID's.
Thus, my crazy queries. This data has been getting collected for over 17
years! I'm just going to John Henry it (sledge hammer my way through the
data).
--Rich
"Tom Moreau" wrote:

> I started life as a scientist. One of my first discoveries was that
> there's
> no money in scientific research, so I made a "lateral" move to IT. I had
> a
> lot of computing background while an undergrad and as a grad. It's
> amazing
> how many people are in IT that didn't start there.
> Keep riding that bike. I take the stairs wherever I can - 2 at a time, if
> I'm inclined. ;-)
> I hear ya about the data. I had an assignment with a police database.
> They
> delete nothing - they just buy a bigger server!
> --
> 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:01A5DAB3-AF6C-4E35-A5F2-621C0B9CCBC1@.microsoft.com...
> This reply is in lieu of the caffeine :). I did the whole route in
> aviation,
> CFI, miliatary, commercial (part 135). I wasn't really making much
> headway
> after a while, so I threw the towel in on commercial aviation and went for
> DB
> programming (EE major in college). I just stay current for part 91
> flying.
> But having the sitdown 8 hrs a day job, drinking caffeine for the last
> bunch
> of years, has taken its toll on me. Now I have to keep an eye on the BP.
> So
> I ride my bicycle to the workplace.
> As for the project I am working on, man, the data is like an ocean.
> Nothing
> gets deleted, just updated, added, and moved around to various tables.
> The
> architecture is OK, normalizatin OK (data gets replicated - that's OK).
> But
> there are so many twists that I have to compensate for, so I have to write
> these crazy queries. Even so, this is still working out a little better
> than the aviation scene for me.
> --Rich
> "Tom Moreau" wrote:
>
>