Dom Sinclair Dom Sinclair - 7 months ago 10
SQL Question

What is the most efficient way to identify rows with differing values

In a database I have two linked tables that store records of fish landings. A business required is that these landings are priced once a week and then posted a week later to allow those individuals for whom payment will then be made to check the paperwork. The system has generally worked well over the years but last week a user managed somehow to change a record such that one particular species and size of fish was priced differently, and because of that it prevented other operations from occurring.

I now need to add an extra validation check (even though the chances of a repeat incident are slim). To that end I have contrived some false data replicating the issue and designed the first simple part of a query to pick it up.

This is the first part of the query;

SELECT DISTINCT
ld.ProductId, ld.UnitPrice
FROM
LandingDetails ld
JOIN
LandingHeaders lh ON ld.LandingId = lh.LandingId
WHERE
lh.LandingDate1 BETWEEN '20160313' AND '20160319'


And here is an example of the type of records returned;

enter image description here

As you can see there are ProductId's listed with different prices. From this point I would like to amend this so that instead of returning all the distinct ProductId's and prices from the date period, it just returns those ProductId's where there are two distinct prices. What I'm looking for is the most efficient way in SQL to achieve this goal. I don't need the prices, it's sufficient just to know which productId's will need alteration of their prices in a separate procedure I have yet to compose.

Answer

The most straightforward method would be to wrap your query with an outer query that utilizes a HAVING clause:

SELECT q.ProductId 
FROM (
SELECT DISTINCT 
    ld.ProductId, ld.UnitPrice 
FROM 
    LandingDetails ld 
JOIN 
    LandingHeaders lh ON ld.LandingId = lh.LandingId
WHERE 
    lh.LandingDate1 BETWEEN '20160313' AND '20160319'
) q 
GROUP BY q.ProductId 
HAVING COUNT(1) >= 2