Partition by filter

Question

Hey Team

I have table where A values comes then E value should be filter or else E value should be there partition by data and mpr column

expected values

Answer 1

Check if this query works, or give counterexamples:

select mpr, Date1, [Read] from MyTable
except
select mpr, Date1, 'E' from MyTable where [Read] = 'A'
order by Date1

Answer 2

Please try this query:

;with cte as (select *, row_number() over (partition by mpr, Date1 order by read) as Rn from Results)
select * from cte where Rn = 1

Answer 3

This answer is coming late. I composed it, but apparently forgot to click the Post button.

CREATE TABLE #temp (mpr    varchar(30) NOT NULL,
                    Date1  date        NOT NULL,
                    read_  char(1)     NOT NULL)
INSERT #temp (mpr, Date1, read_)
   VALUES('test', '20040101', 'A'),
         ('test', '20040101', 'E'),
         ('test', '20050101', 'A'),
         ('test', '20050101', 'E'),
         ('test', '20060101', 'A'),
         ('test', '20070101', 'E')
go
SELECT * FROM #temp
DELETE #temp
FROM   #temp a
WHERE  read_ = 'E'
  AND  EXISTS (SELECT *
               FROM   #temp b
               WHERE  b.mpr = a.mpr
                 AND  b.Date1 = a.Date1
                 AND  b.read_ = 'A')
go
SELECT * FROM #temp
go
DROP TABLE #temp