I have a fairly complex SP with 3 tables the idea being to display a Region, Client and Event
I want to show only the last event for each client
I have replaced my event table with a View which returns the TOP 1 but all I get is the most recent of all records
I want eg:
Region 1, Client 1, Most recent event
Region 1, Client 2, Most recent event
Region 1, Client 3, Most recent event
Not sure of the structure of your tables, but maybe you're looking for something like this?
SELECT RegionName, ClientName, EventName FROM Regions r INNER JOIN Clients c ON r.ClientID=c.ClientID INNER JOIN Events e ON c.ClientID=e.EventID WHERE EventDate=(SELECT MAX(EventDate) FROM Events WHERE ClientID=c.ClientID)
|||Just the job Many thanks , I was not thinking right, this is a simplified version, unfortunatly I needed two MAX fields one for Date the other for EventID as the last ID may not be the most recent Date
exmple below gets all events with the most recent date then picks the most recent id from the most recent dates,
SELECT dbo.tblClients.FName, dbo.tblClients.SName, dbo.tblOCompEvents.CompEventID, dbo.tblOCompEvents.StartDate,
dbo.tblOCompEvents.IRef, dbo.tblClients.ClientID, dbo.tblOCompEvents.Status
FROM dbo.tblOCompEvents INNER JOIN
dbo.tblClients ON dbo.tblOCompEvents.ClientID = dbo.tblClients.ClientID
WHERE (dbo.tblOCompEvents.StartDate IN
(SELECT MAX(StartDate) AS sd
FROM dbo.tblOCompEvents AS tblOCompEvents_1
WHERE (ClientID = dbo.tblOCompEvents.ClientID))) AND (dbo.tblOCompEvents.CompEventID IN
(SELECT MAX(CompEventID) AS ceid
FROM dbo.tblOCompEvents AS tblOCompEvents_1
WHERE (ClientID = dbo.tblOCompEvents.ClientID))) AND (dbo.tblOCompEvents.Status = 25)
ORDER BY dbo.tblClients.ClientID
