SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE [Tab] (num int NULL) GO SET ANSI_NULLS, QUOTED_IDENTIFIER ON GO DROP PROCEDURE IF EXISTS delete_doubles_from_heap GO /* =========== Task "delete_doubles_from_heap":================== There is a table named Tab There is only one column named num. This column is not unique. Some values can be repeated. You must write some code to delete doubles from table Tab. Note: You cannot use INSERT or UPDATE, only DELETE command is allowed. Note: You cannot modify structure of Tab =========================================================*/ CREATE PROCEDURE delete_doubles_from_heap AS BEGIN /* Modify SQL code below to solve the task: */ WITH D AS (SELECT rn = ROW_NUMBER() OVER(partition by num order by num) from Tab ) delete D where rn > 2 END GO DECLARE @LOG NVARCHAR(1000) ------- TEST 1 ------- PRINT N'***** delete_doubles_from_heap *****' PRINT N'' PRINT N'test 1: preparing numbers in Tab: 1 1' TRUNCATE TABLE Tab; INSERT INTO Tab VALUES (1), (1); PRINT N'test 1: RUN your qst.delete_doubles_from_heap...' DECLARE @expected_result nvarchar(1000) = N'1' SET @LOG = N'test 1: expected result: ' + @expected_result PRINT @LOG DECLARE @actual_result NVARCHAR(1000) = N'' SELECT TOP(10) @actual_result = @actual_result + N' ' + CAST(num as nvarchar(100)) FROM Tab ORDER BY num SET @actual_result = LTRIM(@actual_result) DECLARE @print_actual_result nvarchar(max) SET @print_actual_result = N'test 1: actual result: ' + @actual_result PRINT @print_actual_result