Advertisement
Guest User

Untitled

a guest
Mar 31st, 2022
191
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.73 KB | None | 0 0
  1.  
  2. IF OBJECT_ID('tempdb.dbo.#tbl') IS NOT NULL
  3.         DROP TABLE #tbl
  4.  
  5. CREATE TABLE #tbl (
  6. [a] VARCHAR(1) NOT NULL
  7. ,[b] VARCHAR(1) NOT NULL)
  8.  
  9. INSERT INTO #tbl
  10. (
  11.     [a],
  12.     [b]
  13. )
  14. VALUES
  15. ('1','x'),
  16. ('1','x'),
  17. ('2','y'),
  18. ('2','y'),
  19. ('3','z'),
  20. ('3','z')
  21.  
  22. DELETE tab
  23. FROM (SELECT ROW_NUMBER() OVER(ORDER BY [t].[a]) AS row_num
  24.     ,t.*
  25.     FROM [#tbl] t) tab
  26. INNER JOIN (SELECT
  27.             [tble].*,
  28.             RANK() OVER(PARTITION BY [tble].[b] ORDER BY [tble].[ROW_ID]) AS RK
  29.             FROM (
  30.                 SELECT
  31.                 ROW_NUMBER() OVER(ORDER BY [tbl].[a]) AS ROW_ID,
  32.                 [tbl].*
  33.                 FROM [#tbl] AS tbl
  34.                 ) AS tble
  35.             ) RK_TBL
  36.             ON [RK_TBL].[a] = [tab].[a]
  37.             AND [RK_TBL].[ROW_ID] = [tab].[row_num]
  38.             AND [RK_TBL].[RK] > '1'
  39.                    
  40. SELECT * FROM [#tbl]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement