• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

do not include original row, only the duplicate row

tables are linked by dept_name

 CREATE TABLE users (
    user_id int(20) AUTO_INCREMENT NOT NULL,
    full_name varchar(50),
    telephone varchar(15),
    dept_id int(20),
    dept_name varchar(50),  
    users.dept_id
   PRIMARY KEY (user_id)
  )
 

  CREATE TABLE dept (
    dept_id INT(11) AUTO_INCREMENT NOT NULL,  
    dept_name VARCHAR(50),
    num_employees INT(11),
   PRIMARY KEY (dept_id)
  )




want all the columns for duplicate "dept_name"
   in the dept table.  do not include original row,  Only  include the duplicate row.
  the original row is the row that is entered first.
0
rgb192
Asked:
rgb192
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Lara FEACommented:
Not sure I understand why you show table users, but
since you have identity column you can assume that one with smallest id is first one

select dupl.*,first.dept_id    from dept  dupl
   join (select dept_name , min(dept_id ) dept_id  from dept  group by dept_name)  first
on d. dept_name =first.dept_name and d.dept_id >first.dept_id
0
 
Pratima PharandeCommented:
this will give you all dupliacte rows

select max(dept_id) , dept_name, num_employees
from dept
group by dept_name, num_employees
0
 
rgb192Author Commented:
Unknown column 'd.dept_name' in 'on clause'
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rgb192Author Commented:
select max(dept_id) , dept_name, num_employees
from dept
group by dept_name, num_employees


this doesnt have data from the users table
0
 
Lara FEACommented:

select dupl.*,first.dept_id    from dept  dupl
   join (select dept_name , min(dept_id ) dept_id  from dept  group by dept_name)  first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id
0
 
sachinpatil10dCommented:
try this.

select * from (
select *, ROW_NUMBER() over(partition by dept_name order by dept_name) iCount from dept)t
where iCount >1
0
 
sachinpatil10dCommented:
select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
0
 
Pratima PharandeCommented:
select u.* , d.* from
users inner join

 (select max(dept_id) as dept_id , dept_name, num_employees
from dept
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
0
 
rgb192Author Commented:
Unknown column 'd.dept_name' in 'on clause'
select dupl.*,first.dept_id    from dept  dupl
   join (select dept_name , min(dept_id ) dept_id  from dept  group by dept_name)  first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id


both these queries generate errors
select * from (
select *, ROW_NUMBER() over(partition by dept_name order by dept_name) iCount from dept)t
where iCount >1

select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1




closest to being right
but want num_employees(sum)
select u.* , d.* from
users u inner join
 (select max(dept_id) as dept_id , dept_name, num_employees
from dept d
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
0
 
Lara FEACommented:

there is no d.dept_name in this query. query is correct


If you need sum

select dupl. dept_name, sum(dupl.num_employees )
from dept  dupl
   join (select dept_name , min(dept_id ) dept_id  from dept  group by dept_name)  first
on dupl. dept_name =first.dept_name and dupl.dept_id >first.dept_id

But you are really should cleanup your dept table, update dept_id and counts, and delete duplicate. Then use dept_id for joins and make sure that no duplicates are inserted - make deptName unique index for example
0
 
Pratima PharandeCommented:
select  u.*, sum(d.num_employees) from
users u inner join
 (select max(dept_id) as dept_id , dept_name, num_employees
from dept d
group by dept_name, num_employees ) d
on u.dept_name = d.dept_name
0
 
sachinpatil10dCommented:
can you give the error details for the following query...

select * from (
select *, ROW_NUMBER() over(partition by dept_name, num_employees order by dept_name, num_employees) iCount from dept)t
where iCount >1
0
 
rgb192Author Commented:
Thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now