How to group this query so it behaves correctly?

Question

I have a table called Stock and another called Listed, within the Stock Table is a Status Code that indicates when something is at the front of the queue of items of stock - I want to be able to find the most recently added item and set this to be the "front of queue" status.
For example to get all the items listed and then order them by the one most recently listed I would use this query:

SELECT SKU FROM Stock
INNER JOIN Listed
ON Listed.ListingID = Stock.ListingID
WHERE Stock.StatusCode = 2
ORDER BY Listed.ListDate

However I want to find all the items in my Stock table which need to be at the front of the queue - ie. have a StatusCode of 1 where those items have no SKU with a StatusCode of 1
e.g. I have a few items with various ProductCodes in the Stock table but can have StatusCodes of 1s and 2s - where the 1 indicates the first item in the queue, and 2 indicates the rest of the items with the same ProductCode.

How do I write my query to set all those items which need a StatusCode of 1 where anything with a given ProductCode has nothing with a status code of 1?
I want to set the most recently added Stock item listed with a Status Code of 1 as I have to reset them all to 2 as part of a maintainence process and need to restore the "front-of-queue" item.

Most Recently Added: ListDate
StatusCode: 1 (Front of Queue), 2 (Other Items in Stock of same Product Code)

---

Here is some sample Data

Stock Table

SKU    ProductCode    StatusCode  
1      111111         1  
2      111111         2  
3      222222         1  
4      222222         2  
5      333333         2  
6      333333         2

Listed Table

ListID    SKU    ListDate
01        1      01/01/2009
02        2      02/01/2009
03        3      03/01/2009
04        4      04/01/2009
05        5      05/01/2009
06        6      06/01/2009

In the Stock Table SKU 6 with the ProductCode 333333 has two items with the same StatusCode, I want to set the one with the most recent ListDate from the Listed Table to StatusCode 1. This would apply to all other cases of this where I need the most recently added item to have this StatusCode

Answer 1

This is a variation of pick-a-winner... it's pick-all-losers.

Here's the gist. There are several records with a common value, but one record is special - it is the winner. The rest of the records with that common value are losers.

For example, this query picks a winner (per name) from Customers by using the lowest id. It does this by defining what a winner is in the subquery.

SELECT *
FROM Customers c1
WHERE 
(
SELECT Min(CustomerID)
FROM Customers c2
WHERE c2.Name = c1.Name
GROUP BY c2.Name
) =  c1.CustomerID

Then picking the losers is a simple change:

SELECT *
FROM Customers c1
WHERE 
(
SELECT Min(CustomerID)
FROM Customers c2
WHERE c2.Name = c1.Name
GROUP BY c2.Name
) !=  c1.CustomerID

Answer 2

UPDATE S1
SET S1.StatusCode = 1
FROM Stock S1
LEFT JOIN Stock S2
  ON (S1.ProductCode = S2.ProductCode
      AND S2.StatusCode = 1)
JOIN Listed L1
  ON (S1.SKU = L1.SKU)
WHERE S2.StatusCode IS NULL
 AND L1.ListDate =
 ( SELECT MIN(L2.ListDate)
     FROM Listed L2
    WHERE L1.SKU = L2.SKU )

Sometimes you say you want to "find" such items (that I guess would be a SELECT) and sometimes you say you want to "set" their status code -- I've taken the latter operation because it seems a better match for the problem you describe, whence the UPDATE.

Also, it's not clear what you want to do when multiple otherwise-satisfactory items have identical dates and thus it's impossible to uniquely define the latest one; maybe other consraints in your situation make that impossible? Here I'm setting all of their status codes, of course it would also be possible to set none of them or a somewhat arbitrarily chosen one (by ordering on some other criteria?).

Answer 3

This is a variation on a common theme. A similar application to this type of query is used to deal with duplicate rows. In this senario you might want to delete all but one rows of a set.

This query solves you problem:

DECLARE @Stock AS TABLE (SKU Bigint,ProductCode Bigint,StatusCode Bigint)

INSERT INTO @Stock VALUES (1,111111,1) INSERT INTO @Stock VALUES (2,111111,2) INSERT INTO @Stock VALUES (3,222222,1) INSERT INTO @Stock VALUES (4,222222,2) INSERT INTO @Stock VALUES (5,333333,2) INSERT INTO @Stock VALUES (6,333333,2)

DECLARE @Listed AS TABLE (ListID Bigint,SKU Bigint,ListDate DateTime)

INSERT INTO @Listed VALUES (1,1,'01/01/2009') INSERT INTO @Listed VALUES (2,2,'02/01/2009') INSERT INTO @Listed VALUES ( 3,3,'03/01/2009') INSERT INTO @Listed VALUES ( 4,4,'04/01/2009') INSERT INTO @Listed VALUES ( 5,5,'05/01/2009') INSERT INTO @Listed VALUES ( 6,6,'06/01/2009')

UPDATE @Stock SET StatusCode = 1 FROM @Stock AS T1 INNER JOIN @Listed AS T2 ON T1.SKU = T2.SKU WHERE T1.SKU IN

(SELECT TOP 1 T3.SKU FROM @Stock AS T3 INNER JOIN @Listed AS T4 ON T3.SKU = T4.SKU AND T3.ProductCode = T1.ProductCode ORDER BY ListDate)

AND ProductCode IN (SELECT DISTINCT ProductCode FROM @Stock AS S1 WHERE 1 NOT IN (SELECT DISTINCT StatusCode FROM @Stock AS S2 WHERE S2.ProductCode = S1.ProductCode))