SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table t ( user_id int, value_1 int, value_2 int ); insert into t values (99, 50, 1), (99, 50, 1), (100, 50, 1), (100, 50, 2), (101, 50, 1), (101, 50, 1), (101, 50, 2), (101, 50, 2), (102, 50, 2), (102, 50, 3); select * from t; SELECT user_id, value_1, value_2, ROW_NUMBER () OVER (partition by user_id, value_1 ORDER BY value_2) AS row_num FROM t; with t1 as( SELECT user_id, value_1, value_2, ROW_NUMBER () OVER (partition by user_id, value_1 ORDER BY value_2) AS row_num FROM t ) select t.*, t1.*, (t1.user_id = t.user_id and t1.value_1 = t.value_1 and t1.value_2 = t.value_2 and t1.row_num > 1) from t join t1 on t1.user_id = t.user_id and t1.value_1 = t.value_1 and t1.value_2 = t.value_2; /* delete from t using t1 where t1.user_id = t.user_id and t1.value_1 = t.value_1 and t1.value_2 = t.value_2 and t1.row_num > 1; */ select * from t;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear