Solved

do not include original row, only the duplicate row

Posted on 2011-09-19
13
200 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now