Solved

do not include original row, only the duplicate row

Posted on 2011-09-19
13
202 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Data via Excel--performance issues 2 55
Not selecting duplicate data 6 55
SQL Server 2012 express 24 38
CREATE DATABASE ENCRYPTION KEY 1 64
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 this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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