In Microsoft SQL Server, how to keep distinct rows for only a specific value?

6vl6ewon 于 2023-04-10 发布在 SQL Server

关注(0)|答案(3)|浏览(130)

I have a table of events. I would like to eliminate certain values which appear multiple times, e.g.

CreateDate  EventCode
------------------------
2023-03-20  EMA
2023-03-21  EMD
2023-03-22  EMD
2023-03-22  EMF
2023-03-23  EMI
2023-03-24  EMI

The EMI must only appear once and only the first EMI from 23 March. Other duplicates like EMD can remain.

So the result should be

CreateDate  EventCode
------------------------
2023-03-20  EMA
2023-03-21  EMD
2023-03-22  EMD
2023-03-22  EMF
2023-03-23  EMI

A simple group by will eliminate the duplicate EMDs

Here my attempt with sample SQL:

DROP TABLE IF EXISTS #Movements

CREATE TABLE #Movements 
(
    CreateDate datetime2,
    EventCode nvarchar(3)
)

INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-20', 'EMA')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-21', 'EMD')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-22', 'EMD')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-22', 'EMF')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-23', 'EMI')
INSERT INTO #Movements (CreateDate, EventCode) VALUES ('2023-03-24', 'EMI')

SELECT * FROM #Movements

SELECT EventCode 
FROM #Movements
GROUP BY EventCode

sql-server

来源：https://stackoverflow.com/questions/75887771/in-microsoft-sql-server-how-to-keep-distinct-rows-for-only-a-specific-value

3条答案

按热度按时间

x6yk4ghg1#

This will handle the cases where the EMI can be found 2 times or more :

select t.*
from #Movements t
LEFT JOIN (
  SELECT EventCode, MIN(CreateDate) as CreateDate
  FROM #Movements
  WHERE EventCode = 'EMI'
  GROUP BY EventCode
) s on s.EventCode = t.EventCode and t.CreateDate > s.CreateDate
where s.CreateDate is null

Demo here

赞(0）回复(0）举报 2023-04-10

92dk7w1h2#

It can be just

SELECT distinct case EventCode    
                when 'EMI' then min(CreateDate) over(partition by EventCode) 
                else CreateDate end CreateDate, EventCode 
FROM #Movements

赞(0）回复(0）举报 2023-04-10

nmpmafwu3#

You just need to group by both columns, but null out CreateDate for rows where EventCode = 'EMI' . Then take MIN(CreateDate) for those rows instead.

SELECT
  ISNULL(CASE WHEN EventCode <> 'EMI' THEN CreateDate END, MIN(CreateDate)) AS CreateDate,
  EventCode
FROM #Movements
GROUP BY
  EventCode,
  CASE WHEN EventCode <> 'EMI' THEN CreateDate END;

db<>fiddle

赞(0）回复(0）举报 2023-04-10

我来回答

In Microsoft SQL Server, how to keep distinct rows for only a specific value?

3条答案

相关问题

热门标签

最新问答