Limiting specific records within a query 1

[johnrowse]

Hi

Does any one know of a way to achieve the following?

I have a query that returns the results I want, except there are duplicates due to a related table that can have many related records.

I am looking to ensure only one record is returned for any found [tblPump.lPumpID] ordered by [tblPumpMovement.dDateIn]. Apologies in advance if I have not provided enough information so far, for your help...

My query so far is:

SELECT DISTINCT
tblPump.lPumpID, tblPump.sPumpSerialNumber, tblPump.lPumpModelID, tblPump.lClientID, tblPump.bDeactivated, tblPump.bDeleted,
tblPumpMovement.lPumpMovementID, tblSite_1.sSiteName, tblSite_1.lSiteID, tblRegion.sRegion, tblPumpMovement.bPumpOut, tblPumpPoint.lPumpPointID,
tblPumpMovement.dDateOut, tblPumpMovement.dDateIn
FROM tblPumpPoint INNER JOIN
tblSamplePoint ON tblPumpPoint.lSamplePointID = tblSamplePoint.lSamplePointID INNER JOIN
tblSite AS tblSite_1 ON tblSamplePoint.lSiteID = tblSite_1.lSiteID INNER JOIN
tblRegion ON tblSite_1.lRegionID = tblRegion.lRegionID LEFT OUTER JOIN
tblPumpMovement ON tblPumpPoint.lPumpPointID = tblPumpMovement.lPumpPointID RIGHT OUTER JOIN
tblRegion AS tblRegion_1 INNER JOIN
tblSite ON tblRegion_1.lRegionID = tblSite.lRegionID INNER JOIN
tblClient ON tblRegion_1.lClientID = tblClient.lClientID INNER JOIN
tblPump ON tblClient.lClientID = tblPump.lClientID ON tblPumpMovement.lPumpID = tblPump.lPumpID
WHERE (tblSite.lSiteID IN
(SELECT value
FROM dbo.fn_Split(@SiteIDs, ',') AS fn_Split_1)) AND (tblPumpMovement.bPumpOut = 1) OR
(tblSite_1.lSiteID IN
(SELECT value
FROM dbo.fn_Split(@SiteIDs, ',') AS fn_Split_1)) OR
(tblSite_1.lSiteID IS NULL)
ORDER BY tblPumpMovement.dDateIn, tblPumpMovement.dDateOut, tblPump.sPumpSerialNumber

 

[lameid]

Seems like you should be able to get to (join) tblPump from tblPumpPoint which should have just one pump related... Just guessing based on what I expect the design should be.

Otherwise you would need to decide which pump you want to use if multiples are available and make a query to determine that. That query becomes a sub-query replacing your existing use of tblPump.

 

[johnrowse]

Thanks for your replay lameid.

My design means they are related by a mapping table, rather than directly [tblPumpMovement]

As a pump can have unlimited movement, I need to return only the 'last' movement recorded for any given pump. So I have ordered by [tblPumpMovement.dDateIn] but, how I am unsure how to remove all but the Top(1) for any given distinct [tblPump.lPumpID]. The returned query may contain more than one PumpID (which is desired), but only one of each, regardless.

 

[lameid]

Without understanding the structure it is hard to say... You're going to need MAX([tblPumpMovement.dDateIn])and grouping by other things then inner join that back to the tblPumpMovement to get the appropriate pumpID and that is assuming there is one dDateIN for whatever your grouping and that will be your subquery. You could also use a temp table instead of a sub-query but I doubt it would make any difference.

 

[johnrowse]

Thanks again, lameid

I have added the sub query as you suggested (I think), but now I get an error message: "Error Column tblPumpMovement.lPumpMovement is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" Adding that causes to flick the next column in tblPumpMovement, but I don't want to group on these do I? I just want max(dDateIn) grouped by lPumpID (plus the standard columns for use as before).... ??

SELECT DISTINCT 
                         tblPump.lPumpID, tblPump.sPumpSerialNumber, tblPump.lPumpModelID, tblPump.lClientID, tblPump.bDeactivated, tblPump.bDeleted, 
                         tblPM.lPumpMovementID, tblSite_1.sSiteName, tblSite_1.lSiteID, tblRegion.sRegion, tblPM.bPumpOut, tblPumpPoint.lPumpPointID, 
                         tblPM.dDateOut, tblPM.dDateIn
FROM            tblPumpPoint INNER JOIN
                         tblSamplePoint ON tblPumpPoint.lSamplePointID = tblSamplePoint.lSamplePointID INNER JOIN
                         tblSite AS tblSite_1 ON tblSamplePoint.lSiteID = tblSite_1.lSiteID INNER JOIN
                         tblRegion ON tblSite_1.lRegionID = tblRegion.lRegionID LEFT OUTER JOIN
                         (SELECT        lPumpMovementID, lPumpID, lPumpPointID, bExternalPumpLocation, lExternalPumpLocationID, MAX(dDateIn) AS Date, lPulseCountIn, bPumpOut, dDateOut, 
                         lPulseCountOut, bDeactivated, bDeleted, dDateIn
FROM            tblPumpMovement
GROUP BY lPumpID) tblPM ON tblPumpPoint.lPumpPointID = tblPM.lPumpPointID RIGHT OUTER JOIN
                         tblRegion AS tblRegion_1 INNER JOIN
                         tblSite ON tblRegion_1.lRegionID = tblSite.lRegionID INNER JOIN
                         tblClient ON tblRegion_1.lClientID = tblClient.lClientID INNER JOIN
                         tblPump ON tblClient.lClientID = tblPump.lClientID ON tblPM.lPumpID = tblPump.lPumpID
WHERE        (tblSite.lSiteID IN
                             (SELECT        value
                               FROM            dbo.fn_Split(@SiteIDs, ',') AS fn_Split_1)) AND (tblPM.bPumpOut = 1) OR
                         (tblSite_1.lSiteID IN
                             (SELECT        value
                               FROM            dbo.fn_Split(@SiteIDs, ',') AS fn_Split_1)) OR
                         (tblSite_1.lSiteID IS NULL)
ORDER BY tblPM.dDateIn, tblPM.dDateOut, tblPump.sPumpSerialNumber

 

[lameid]

The sub-query should look something like this...

(Select tblPumpMovement.*
From tblPumpMovement Inner Join (Select lPumpID, MAX(dDateIn) as MaxdDateIN
                                 From tblPumpMovement 
                                 Group BY lPumpID
                                 ) B
      ON B.lPumpPointID  = tblPumpPoint.lPumpPointID 
         and B.MaxdDateIN = tblPumpMovement.dDateIn
) PM

 

[johnrowse]

Thank you so much, lameid

Not only have you resolved my issue, you have taught me a lot about sub-queries!

In case it helps others, I modified the sub-query:

(Select tblPumpMovement.*,B.MaxdDateIN
From tblPumpMovement Inner Join 
	(Select lPumpID, MAX(dDateIn) as MaxdDateIN
	From tblPumpMovement
	Group BY lPumpID) B
ON b.lPumpID = tblPumpMovement.lPumpID and  B.MaxdDateIN = tblPumpMovement.dDateIn)

This 'filters' a mapping table so only the latest mapped record for each item is returned

This is then used in the full query to bring back the details about the items above, including the mapped details:

SELECT DISTINCT 
                         tblPump.lPumpID, tblPump.sPumpSerialNumber, tblPump.lPumpModelID, tblPump.lClientID, tblPump.bDeactivated, tblPump.bDeleted, tblPM.lPumpMovementID, 
                         tblSite_1.sSiteName, tblSite_1.lSiteID, tblRegion.sRegion, tblPM.bPumpOut, tblPumpPoint.lPumpPointID, tblPM.dDateOut, tblPM.dDateIn
FROM            tblPumpPoint INNER JOIN
                         tblSamplePoint ON tblPumpPoint.lSamplePointID = tblSamplePoint.lSamplePointID INNER JOIN
                         tblSite AS tblSite_1 ON tblSamplePoint.lSiteID = tblSite_1.lSiteID INNER JOIN
                         tblRegion ON tblSite_1.lRegionID = tblRegion.lRegionID LEFT OUTER JOIN
                             (SELECT        tblPumpMovement.lPumpMovementID, tblPumpMovement.lPumpID, tblPumpMovement.lPumpPointID, tblPumpMovement.bExternalPumpLocation, 
                                                         tblPumpMovement.lExternalPumpLocationID, tblPumpMovement.dDateIn, tblPumpMovement.lPulseCountIn, tblPumpMovement.bPumpOut, 
                                                         tblPumpMovement.dDateOut, tblPumpMovement.lPulseCountOut, tblPumpMovement.bDeactivated, tblPumpMovement.bDeleted, B.lPumpID AS Expr1, 
                                                         B.MaxdDateIN
                               FROM            tblPumpMovement INNER JOIN
                                                             (SELECT        lPumpID, MAX(dDateIn) AS MaxdDateIN
                                                               FROM            tblPumpMovement AS tblPumpMovement_1
                                                               GROUP BY lPumpID) AS B ON B.lPumpID = tblPumpMovement.lPumpID AND B.MaxdDateIN = tblPumpMovement.dDateIn) AS tblPM ON 
                         tblPumpPoint.lPumpPointID = tblPM.lPumpPointID RIGHT OUTER JOIN
                         tblRegion AS tblRegion_1 INNER JOIN
                         tblSite ON tblRegion_1.lRegionID = tblSite.lRegionID INNER JOIN
                         tblClient ON tblRegion_1.lClientID = tblClient.lClientID INNER JOIN
                         tblPump ON tblClient.lClientID = tblPump.lClientID ON tblPM.lPumpID = tblPump.lPumpID
WHERE        (tblSite.lSiteID IN
                             (SELECT        value
                               FROM            dbo.fn_Split(@SiteIDs, ',') AS fn_Split_1)) AND (tblPM.bPumpOut = 1) OR
                         (tblSite_1.lSiteID IN
                             (SELECT        value
                               FROM            dbo.fn_Split(@SiteIDs, ',') AS fn_Split_1)) OR
                         (tblSite_1.lSiteID IS NULL)
ORDER BY tblPM.dDateIn, tblPM.dDateOut, tblPump.sPumpSerialNumber

Thanks again, lameid