Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF OBJECT_ID('tempdb.dbo.#tbl') IS NOT NULL
- DROP TABLE #tbl
- CREATE TABLE #tbl (
- [a] VARCHAR(1) NOT NULL
- ,[b] VARCHAR(1) NOT NULL)
- INSERT INTO #tbl
- (
- [a],
- [b]
- )
- VALUES
- ('1','x'),
- ('1','x'),
- ('2','y'),
- ('2','y'),
- ('3','z'),
- ('3','z')
- DELETE tab
- FROM (SELECT ROW_NUMBER() OVER(ORDER BY [t].[a]) AS row_num
- ,t.*
- FROM [#tbl] t) tab
- INNER JOIN (SELECT
- [tble].*,
- RANK() OVER(PARTITION BY [tble].[b] ORDER BY [tble].[ROW_ID]) AS RK
- FROM (
- SELECT
- ROW_NUMBER() OVER(ORDER BY [tbl].[a]) AS ROW_ID,
- [tbl].*
- FROM [#tbl] AS tbl
- ) AS tble
- ) RK_TBL
- ON [RK_TBL].[a] = [tab].[a]
- AND [RK_TBL].[ROW_ID] = [tab].[row_num]
- AND [RK_TBL].[RK] > '1'
- SELECT * FROM [#tbl]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement