Solved

do not include original row, only the duplicate row

Posted on 2011-09-19
13
204 Views
Last Modified: 2012-05-12
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
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 36564775
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36564783
this will give you all dupliacte rows

select max(dept_id) , dept_name, num_employees
from dept
group by dept_name, num_employees
0
 

Author Comment

by:rgb192
ID: 36564785
Unknown column 'd.dept_name' in 'on clause'
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rgb192
ID: 36564792
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 36564807

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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36564808
try this.

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

Expert Comment

by:sachinpatil10d
ID: 36564815
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36564826
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
 

Author Comment

by:rgb192
ID: 36564902
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 36564942

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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36564948
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
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36578323
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
 

Author Closing Comment

by:rgb192
ID: 36589025
Thanks
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question