Help with Max Date please ...

  • Thread starter Thread starter MostlyH2O
  • Start date Start date
M

MostlyH2O

Hi Folks,

I have a query that joins 3 tables. One of the tables (SalaryData) has data
where there may be duplicate records with different dates. Of those
duplicate records, I want the query to show me only the one with the latest
date. The query below shows me all the records - including the duplicates
with older dates. I have tried various ways of using the MAX(DateField)
But can't get it to work. I need to keep the joins the way they are to
retain the proper recordset. This will be implimented on an ASP page.

Can anyone help me correct the query below - so I only get the newest
records from the table "SalaryData"- where the field "SalaryData.BenchID" is
the same? (the date field is called "SalaryData.Dat")


SELECT SalaryData.BenchID, SalaryData.OrgID, SalaryData.Min, SalaryData.Mid,
SalaryData.Max, SalaryData.Avr, SalaryData.NumPos, SalaryData.OM,
SalaryData.FY, OrgJobs.OrgID, OrgJobs.BenchID, Benchmarks.CatID,
Benchmarks.BenchID, Benchmarks.Benchmark, Max(SalaryData.Dat) AS Expr1
FROM (Benchmarks INNER JOIN OrgJobs ON Benchmarks.BenchID = OrgJobs.BenchID)
LEFT JOIN SalaryData ON OrgJobs.BenchID = SalaryData.BenchID
WHERE (((OrgJobs.OrgID)=1083 Or (OrgJobs.OrgID) Is Null) AND
((Benchmarks.CatID)=40))
GROUP BY SalaryData.BenchID, SalaryData.OrgID, SalaryData.Min,
SalaryData.Mid, SalaryData.Max, SalaryData.Avr, SalaryData.NumPos,
SalaryData.OM, SalaryData.FY, OrgJobs.OrgID, OrgJobs.BenchID,
Benchmarks.CatID, Benchmarks.BenchID, Benchmarks.Benchmark;

This is actually a sub-query which is nested in another query. Together
they give me the correct recordset - except that I get duplicate records
with older dates. I have tried to figure out a way to break up the query
into seperate queries, but then my ASP will need to loop exhaustively.

Thanks very much for any help.

Jack Coletti
St. Petersburg, FL
 
Jack,

I haven't tried this but how about
Having SalaryData.Dat = Max(SalaryData.Dat)
following the GROUP BY? Just use SalarayData.Dat in the SELECT list.
 
Hi Chaim,

Nope - That still returns all records - instead of the most recent ones.
The problem is that the additional fields are causing it to get all records.

In other words -

SELECT Max(SalaryData.Dat), From SalaryData --- This Works
SELECT SalaryData.Field2, Max(SalaryData.Dat), From SalaryData GROUP By
SalaryData.Field2 --- Does Not work If Field 2 is unique in each of the
duplicate records.

Thanks,
Jack
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about this? Use a subquery to get the Max(Dat):

SELECT SD.BenchID,
SD.OrgID,
SD.Min,
SD.Mid,
SD.Max,
SD.Avr,
SD.NumPos,
SD.OM,
SD.FY,
OJ.OrgID,
OJ.BenchID,
BM.CatID,
BM.BenchID,
BM.Benchmark

FROM (Benchmarks As B
INNER JOIN OrgJobs AS OJ
ON BM.BenchID = OJ.BenchID)
LEFT JOIN SalaryData AS SD
ON OJ.BenchID = SD.BenchID

WHERE BM.CatID=40
AND (OJ.OrgID=1083 Or OJ.OrgID Is Null)

-- This subquery should get what you want

AND SD.Dat = (SELECT MAX(Dat) FROM SalaryData
WHERE BenchID = SD.BenchID)


GROUP BY SD.BenchID,
SD.OrgID,
SD.Min,
SD.Mid,
SD.Max,
SD.Avr,
SD.NumPos,
SD.OM,
SD.FY,
OJ.OrgID,
OJ.BenchID,
BM.CatID,
BM.BenchID,
BM.Benchmark;
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlV8ooechKqOuFEgEQI98wCcCJJQLGui8K0HY2QRE9Rq5nY21OAAoILQ
WJxPvxRWPZh6R7J606QB2etc
=Q0p3
-----END PGP SIGNATURE-----
 
I believe that's what I needed. Thanks VERY much for you answers to both my
posts.

clear skies,
Jack
 
From your handle "MostlyH2O", I recall a story / joke:

2 university professors (one old and one young) were in earnest discussion
about their scientific endeavours. A young female student (and of course,
blond / tall / slim / beautiful / extraordinarily curvaceous and
indescribably sexy-looking - rated 11 out of 10, basically) walked by and
the young professor couldn't take his eyes of her and briefly lost track of
the discussion. The old professor got very annoyed and said:

"Scientifically, what you are looking at is very ordinary, mostly water
(more than 90%, anyway)."

The young professor (who is specialised in Computational Physics and skilled
in time-sharing his brain) retorted within microseconds:

"Yeah. However, I am observing / invetigating surface tension and the
effects of movements on surface tension."

--
Van T. Dinh
MVP (Access)




news:[email protected]...
 
Back
Top