SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
-- Step 1: Define the table schema and insert data (same as before) CREATE TABLE student_activities ( id INTEGER, student_id INTEGER, account_id INTEGER, resource_type VARCHAR(50), score INTEGER, quiz_id INTEGER, etl_last_updated_ts TIMESTAMP ); INSERT INTO student_activities (id, student_id, account_id, resource_type, score, quiz_id, etl_last_updated_ts) VALUES (10290795, 1096217, 0, 'Movie', NULL, 0, '2023-08-21 00:47:16.000'), (10290795, 1096217, 8393098, 'Movie', NULL, 0, '2023-08-10 01:22:46.000'), (10291952, 1096217, 0, 'Quiz', 3, 57, '2023-08-21 00:47:16.000'), (10291952, 1096217, 8393098, 'Quiz', 3, 57, '2023-08-10 01:22:46.000'), (10292230, 1096217, 8393098, 'Quiz', 7, 57, '2023-08-10 01:22:46.000'), (10292230, 1096217, 0, 'Quiz', 7, 57, '2023-08-21 00:47:16.000'), (11299627, 1096217, 0, 'Movie', NULL, 0, '2023-08-21 00:47:16.000'), (11299627, 1096217, 8393098, 'Movie', NULL, 0, '2023-08-10 01:22:46.000'), (111400388, 1096217, 8393098, 'Quiz', 6, 753, '2023-08-10 01:22:46.000'), (111400388, 1096217, 0, 'Quiz', 6, 753, '2023-08-21 00:47:16.000'); -- Step 2: Perform deduplication using a CTE with ROW_NUMBER() and DELETE statement WITH dedup_cte AS ( SELECT id, student_id, account_id, resource_type, score, quiz_id, etl_last_updated_ts, ROW_NUMBER() OVER ( PARTITION BY id -- Partition by id to find duplicates ORDER BY etl_last_updated_ts ASC, -- Lowest etl_last_updated_ts first account_id ASC -- Lowest account_id second (if etl_last_updated_ts is tied) ) AS row_num FROM student_activities ) DELETE FROM student_activities WHERE (id, etl_last_updated_ts, account_id) IN ( SELECT id, etl_last_updated_ts, account_id FROM dedup_cte WHERE row_num > 1 -- Keep only the first record in each partition ); -- Step 3: Display the deduplicated data SELECT * FROM student_activities;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear