distinct

[trk1616b]

What am I missing? i think it's simple, but i can't grasp it.

From my SQL pasted below, I get a list of contractors that are listed before 10/1/2005, but not after 2005. This is exactly what I want, but I only want to return 1 value for each contractor (So if contractor XYZ has 5 rows before 10/1/2005, I only want to return 1 row). The 1 row returned also needs to be the most recent one.

I've tried changing the first line to 'Select Distinct Contractor', and this is the list I want, but it only returns the Contractor field and I need all fields.

Thanks in Advance!!



Here's my SQL:

Select *
From SalesTable A
Where EXISTS
(Select *
From SalesTable B
Where A.Contractor = B.Contractor
AND B.[Date] < #10/01/2005# )
AND NOT EXISTS
(Select *
From SalesTable C
Where C.Contractor = A.Contractor
AND C.[Date] > #10/01/2005# )
Order By Contractor

 

[Golom]

Try this (assuming that there is only 1 record on which the MAX(Date) occurs.)

Select *

From  SalesTable A

WHERE

A.Date = (Select MAX(X.[Date])
          From SalesTable X
          Where X.Contractor = A.Contractor)

AND EXISTS 
    (Select * 
    From SalesTable B
    Where A.Contractor = B.Contractor
    AND B.[Date] < #10/01/2005# )
AND NOT EXISTS 
    (Select * 
    From SalesTable C
    Where C.Contractor = A.Contractor
    AND C.[Date] > #10/01/2005# )

Order By Contractor

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

you may try this:
SELECT A.*
FROM SalesTable AS A INNER JOIN (
SELECT Contractor, MAX([Date]) AS LastDate FROM SalesTable
GROUP BY Contractor HAVING MAX([Date]) < #2005-10-01#
) AS B ON A.Contractor = B.Contractor AND A.Date = B.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886