Sort many ids in a table in SQL Server-CodePudding

I have been given a task which I should look on items table and grab first item of 2019 and last item for 2019 and set the active flags on them as active , the query I wrote only I can grab one by one depends on the store, and it takes days to finish if I have no other choice, here is my query in SQL Server:

SELECT *
FROM NODES 
WHERE NODE ID = 5562 
  AND DATE BETWEEN '2019/01/01' AND '2019/12/30'

Basically I need the first and the last item for the year, but the problem is every Node is a specific store which has many record and I have run the query for million of records in many Nodes, is it possible if I for example say OK SQL from the given nodes take first and last item for 2019 and display to me and then update their active flag = 'Y'

Is it possible with a CTE, do I need a CTE at all?

Thank you

CodePudding user response：

If I understood correctly, you could try using a CTE with a windowed function to fetch only the first row from each store after ordering by date in ascending order and the first row from each store after ordering by date in descending order.

For instance :

CREATE TABLE NODES (NodeId int,NodeDate DATETIME2,status NVARCHAR(128))

INSERT INTO NODES(NodeId,NodeDate,Status) VALUES 
(1,'2019/01/01','inactive'),
(1,'2019/03/01','inactive'),
(1,'2019/06/01','inactive'),
(1,'2019/09/01','inactive'),
(1,'2019/12/01','inactive'),
(2,'2019/01/01','inactive'),
(2,'2019/03/01','inactive'),
(2,'2019/06/01','inactive'),
(2,'2019/09/01','inactive'),
(2,'2019/12/01','inactive'),
(3,'2019/01/01','inactive'),
(3,'2019/03/01','inactive'),
(3,'2019/06/01','inactive'),
(3,'2019/09/01','inactive'),
(3,'2019/12/01','inactive')


;WITH cte AS
(
   SELECT status,
         ROW_NUMBER() OVER (PARTITION BY NodeId ORDER BY NodeDate ASC) AS FirstDate,
         ROW_NUMBER() OVER (PARTITION BY NodeId ORDER BY NodeDate DESC) AS LastDate         
   FROM NODES
   WHERE NodeDate >= '2019/01/01' AND NodeDate < '2020/01/01'
)
UPDATE CTE SET status = 'active'
WHERE FirstDate = 1 OR LastDate = 1 


SELECT * FROM NODES

Try it online

Please do note however that this operation can be non deterministic if multiple rows have the same date.