SQL grouping

Question

Hi all
I'm trying to create a query that lists a group of records only when the last record of the group doesn't have a specific field value (trans<>'delete')

key access trans date (mm/dd/yyyy)
1 adt add 01/01/2020
2 adt change 01/02/2020
3 adt delete 01/03/2020
4 btt add 01/01/2020
5 btt change 01/02/2020
6 cdt add 01/01/2020
7 cdt change 01/02/2020
8 cdt delete 01/03/2020

Select * from table1
exist where
(Select access, date
from table1
group by access, date
having trans <> 'delete')

I'm looking for this result: group without a last delete row
4 btt add 01/01/2020
5 btt change 01/02/2020

In my other sql attempts, I'm only able to get the Delete row

Answer 1

Try this:

DECLARE @t Table (
    [key] int, 
    [access] varchar(20), 
    [trans] varchar(20), 
    [date] datetime
);
INSERT INTO @t VALUES
(1, 'adt', 'add', '01/01/2020'),
(2, 'adt', 'change', '01/02/2020'),
(3, 'adt', 'delete', '01/03/2020'),
(4, 'btt', 'add', '01/01/2020'),
(5, 'btt', 'change', '01/02/2020'),
(6, 'cdt', 'add', '01/01/2020'),
(7, 'cdt', 'change', '01/02/2020'),
(8, 'cdt', 'delete', '01/03/2020');

SELECT *
FROM @t
WHERE [access] NOT IN (SELECT [access] FROM @t WHERE [trans] = 'delete');

Answer 2

Hi @JCBond ,

Welcome to Microsoft Q&A!

Please also refer below and check whether it is working.

create Table table1  (  
     [key] int,   
     [access] varchar(20),   
     [trans] varchar(20),   
     [date] datetime);  
  
 INSERT INTO table1 VALUES  
 (1, 'adt', 'add', '01/01/2020'),  
 (2, 'adt', 'change', '01/02/2020'),  
 (3, 'adt', 'delete', '01/03/2020'),  
 (4, 'btt', 'add', '01/01/2020'),  
 (5, 'btt', 'change', '01/02/2020'),  
 (6, 'cdt', 'add', '01/01/2020'),  
 (7, 'cdt', 'change', '01/02/2020'),  
 (8, 'cdt', 'delete', '01/03/2020');  
  
Select *   
from table1 a  
where  exists   
(Select 1 from   
(select access,trans,date  
,ROW_NUMBER() over(partition by access order by date desc) rn  
from table1) b   
where a.access=b.access and rn=1 and trans<>'delete')  

Output:

key	access	trans	date  
4	btt	add	2020-01-01 00:00:00.000  
5	btt	change	2020-01-02 00:00:00.000  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.