Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

do not include original row, only the duplicate row

Posted on 2011-09-19
13
Medium Priority
?
209 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

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…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

926 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