Trying to get the most recent record for each company that is returned
in the following SQL statement. For example, the SQL statement returns:

DatePulled Company Name Revenue
2004-01-01 ABC Corp 1000.0000
2004-02-01 ABC Corp 1234.0000
2004-02-01 XYZ Corp 5000.0000
2004-03-01 XYZ Corp 2567.0000

I need the SQL statement to return the information for ABC Corp on
2004-02-01 and the information for XYZ Corp on 2004-03-01.

The SQL statement follows:

Select total=Sum(PipelineData.Amount), DatePulled AS [Commencement
Date], [Schedule].[StatusCode], [Schedule].[PurchaseType] AS [EQUIPMENT
TYPE], Customers.[Company Name]
From [PipelineData]
Inner Join Schedule on PipelineData.ScheduleID = Schedule.ID
Inner Join Customers on [Schedule].[Customer ID] = Customers.ID
Where (([Schedule].[StatusCode] like '14-ASSIGNED') or
([Schedule].[StatusCode] like '23-SOLD') or ([Schedule].[StatusCode]
like '10-FUNDED%') or ([Schedule].[StatusCode] like '20-PDOFF/MATURED')
or ([Schedule].[StatusCode] like '19-ENFORCEMENT') or
([Schedule].[StatusCode] like '18-EAB'))
GROUP BY [PipelineData].[DatePulled], [Schedule].[StatusCode],
[Schedule].[PurchaseType], [Customers].[Company Name]
Order by [Customers].[Company Name]

*** Sent via Developersdex http://www.developersdex.com ***

Sample data returned

Total DatePulled Status EquipType CompName
268140.8400 2002-06-01 14-ASSIGNED Computer ABC Corp
67035.2100 2002-07-01 14-ASSIGNED Computer ABC Corp
265536.2000 2002-09-01 14-ASSIGNED Agricultural XYZ Corp
265536.2000 2002-10-01 14-ASSIGNED Agricultural XYZ Corp
47447.5900 2002-11-01 14-ASSIGNED Agricultural XYZ Corp
47447.5900 2003-01-01 14-ASSIGNED Agricultural XYZ Corp

Need the query to return
67035.2100 2002-07-01 14-ASSIGNED Computer ABC Corp
47447.5900 2003-01-01 14-ASSIGNED Agricultural XYZ Corp


*** Sent via Developersdex http://www.developersdex.com ***

First, your query and the sample data does not match. It is possible that
the resultset will have multiple values for StatusCode or PurchaseType for a
single value of CompanyName. So please post your DDLs, sample data &
expected results so that others can help you with correct suggestions. For
details refer to www.aspfaq.com/5006.

In general, for a simple SELECT statement which has a single unique column
with duplicated values in another column, the standard way of eliminating
duplications is :

SELECT *
FROM tbl t1
WHERE t1.unique-col = ( SELECT MAX( t2.unique-col )
FROM tbl t2
WHERE t2.dup-col = t1.dup-col ) ;

-- or

SELECT *
FROM tbl t1
INNER JOIN (
SELECT dup-col, MAX( unique-col )
FROM tbl
GROUP BY dup-col ) D ( c1, c2 )
ON t1.unique-col = D.c2 ;

--
Anith

Upon a single glance, the following should do:

SELECT SUM( p1.Amount ) AS "Total",
MAX( p1.DatePulled ) AS "Commencement Date",
s1.StatusCode,
s1.PurchaseType AS "EQUIPMENT TYPE",
c1."Company Name"
FROM PipelineData p1
INNER JOIN Schedule s1
ON p1.ScheduleID = s1.ID
INNER JOIN Customers c1
ON s1."Customer ID" = c1.ID
WHERE s1.StatusCode IN ( '14-ASSIGNED', '23-SOLD',
'20-PDOFF/MATURED', '19-ENFORCEMENT', '18-EAB' )
OR s1.StatusCode LIKE '10-FUNDED%'
GROUP BY s1.StatusCode, s1.PurchaseType, c1."Company Name"
ORDER BY c1."Company Name" ;

-- or a different one..

SELECT SUM( p1.Amount ) AS "Total",
p1.DatePulled AS "Commencement Date",
s1.StatusCode,
s1.PurchaseType AS "EQUIPMENT TYPE",
c1."Company Name"
FROM PipelineData p1
INNER JOIN Schedule s1
ON p1.ScheduleID = s1.ID
INNER JOIN Customers c1
ON s1."Customer ID" = c1.ID
WHERE s1.StatusCode IN ( '14-ASSIGNED', '23-SOLD',
'20-PDOFF/MATURED', '19-ENFORCEMENT', '18-EAB' )
OR s1.StatusCode LIKE '10-FUNDED%'
AND p1.DatePulled = ( SELECT MAX( p2.DatePulled )
FROM PipelineData p2
INNER JOIN Schedule s2
ON p2.ScheduleID = s2.ID
INNER JOIN Customers c2
ON s2."Customer ID" = c2.ID
WHERE s2.StatusCode = s1.StatusCode
AND c2."Company Name" = c1."Company Name" )
GROUP BY p1.DatePulled, s1.StatusCode, s1.PurchaseType, c1."Company Name"
ORDER BY c1."Company Name" ;

--
Anith