SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,788 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have table:
How can I select group of record contain status=open? (Group record with status = sent, received, open, close) and delete the rest.
Thank you. MN
Here is a query. Note that the first part is only to set up the test data. Normally, we prefer that you do this yourself to save our time. We cannot copy from an image, but if you give us a script, we can easily work with that.
DECLARE @T TABLE (ID int NOT NULL PRIMARY KEY,
Status varchar(10) NOT NULL)
INSERT @T (ID, Status)
VALUES(1, 'Sent'), (2, 'Whatever'), (3, 'Extra'), (4, 'Close'),
(5, 'Junk'), (6, 'Close'), (7, 'Seven'),
(8, 'Sent'), (9, 'Nove'), (10, 'Kymmenen'), (11, 'Close'),
(12, 'Douze')
SELECT a.*
FROM @T a
OUTER APPLY (SELECT TOP(1) b.Status
FROM @T b
WHERE b.ID < a.ID
AND b.Status IN ('Sent', 'Close')
ORDER BY b.ID DESC) AS b
WHERE 'Sent' IN (a.Status, b.Status)
First using filter then select