Scribd
Upload
32 views1 page

Query+1+ +script

The document provides SQL queries to fetch duplicate records from a 'users' table. It includes two solutions: one using a subquery with 'ctid' and another using a window function with 'row_number'. The table structure and sample data are also included for context.

Uploaded by

dashbikash34
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
32 views1 page

Query+1+ +script

The document provides SQL queries to fetch duplicate records from a 'users' table. It includes two solutions: one using a subquery with 'ctid' and another using a window function with 'row_number'. The table structure and sample data are also included for context.

Uploaded by

dashbikash34
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

-- Query 1:

Write a SQL query to fetch all the duplicate records from a table.

--Tables Structure:

drop table users;


create table users
(
user_id int primary key,
user_name varchar(30) not null,
email varchar(50));

insert into users values


(1, 'Sumit', 'sumit@[Link]'),
(2, 'Reshma', 'reshma@[Link]'),
(3, 'Farhana', 'farhana@[Link]'),
(4, 'Robin', 'robin@[Link]'),
(5, 'Robin', 'robin@[Link]');

select * from users;

-- Solution 1:

-- Replace ctid with rowid for Oracle, MySQL and Microsoft SQLServer
select *
from users u
where [Link] not in (
select min(ctid) as ctid
from users
group by user_name
order by ctid);

-- Solution 2: Using window function.

select user_id, user_name, email


from (
select *,
row_number() over (partition by user_name order by user_id) as rn
from users u
order by user_id) x
where [Link] <> 1;

You might also like