Max of the Max

  • Thread starter Thread starter Thomas Wright
  • Start date Start date
T

Thomas Wright

LaMerNumber LastName FirstName TransDate code FacilityName
PlacementType
765401 Roldan Mark 3/2/2004 9923201 VENIS CACCAM'S HOMES Level 3 Home
765401 Roldan Mark 2/27/2004 9923301 VENIS CACCAM'S HOMES Level 3 Home
765401 Roldan Mark 2/20/2004 9922201 VENIS CACCAM'S HOMES Level 3 Home
765703 Collins Jake 9/24/2004 9931201 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 9/17/2004 5001 Acacias Care Center Skilled Nursing
Facility
765703 Collins Jake 9/17/2004 201 Acacias Care Center Skilled Nursing
Facility
765703 Collins Jake 8/16/2004 9931301 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 6/12/2004 9080101 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 5/27/2004 5700 Acacias Care Center Skilled Nursing
Facility
765703 Collins Jake 3/10/2004 9923201 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 2/7/2004 9923301 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 2/4/2004 9922201 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 2/2/2004 9925401 Acacias Care Center Skilled
Nursing Facility
765703 Collins Jake 2/13/2004 9923801 Acacias Care Center Skilled
Nursing Facility


this is a hypothetical table with two people with their respective ID
(LaMerNumber) and I want to find the maximum TransDate (transaction date)
for each person. When I use the Max function for TransDate I get a result
for each code. So instead of getting one Maximum transdate I get three in
the case of Mark since there are three separate codes. All I want is the
Maximum of all TransDates per person, i.e. a query as such would have two
records shown, one for Mark and one for Jake.

I have tried many combinations or Max with Aggregate functions but with no
consistent result. Select Distinct does not work.

Any help would be much appreciated.

Tom Wright
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT LaMerNumber, LastName, FirstName, TransDate, code, FacilityName,
PlacementType
FROM table As T
WHERE TransDate = (SELECT Max(TransDate) FROM table
WHERE LaMerNumber = T.LaMerNumber)

There can only be one record w/ the same date per LaMerNumber.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


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

iQA/AwUBQkSEYYechKqOuFEgEQIkGQCgiOwzp7m/3VltzJRJdENzqjQdo2UAnRcH
n87R4iKwIXKszwG44EnTG8El
=jMIN
-----END PGP SIGNATURE-----
 
Back
Top