Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-10-21 : 13:44:04
|
| This is kind of a simple one...How do I get the duplicate records in a tableeg:tbl_emp_detailslets say the columns are emp_id,emp_name,emp_age,emp_address,date_assignedNow 2 queries i need to do is 1. get only the duplicate records2.get the distinct records with max date |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-21 : 13:52:11
|
| SELECT emp_id,emp_name,emp_age,emp_address,date_assignedFROM tbl_emp_detailsGROUP BY emp_id,emp_name,emp_age,emp_address,date_assignedHAVING COUNT(*) > 1SELECT emp_id,emp_name,emp_age,emp_address,MAX(date_assigned_FROM tbl_emp_detailsGROUP BY emp_id,emp_name,emp_age,emp_addressKristen |
 |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-21 : 14:18:45
|
| Thanks a lot...Here both queries are fine . a small difference I was looking for Here lets say in the first query lets say the only difference is emp_id and the rest of the fields are same then Iam seeing only one record for that and I want to see both duplicate records |
|
|
|
jmangione
Starting Member
7 Posts |
Posted - 2004-10-21 : 14:27:45
|
| If I understand you correctly you could try:SELECT max(emp_id),emp_name,emp_age,emp_address,date_assignedFROM tbl_emp_detailsGROUP BY emp_name,emp_age,emp_address,date_assignedYou need to decide what emp_id you'd like to return (max or Min) but that will give you the "unique" data set beyond the identity.Hope this helps! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-21 : 14:41:59
|
| Just leave the emp_id column out of the SELECT and GROUP BY statementsBasically you will get a list of duplicates for the columns which are iin the GROUP BY statement (makes sense to also include them in the SELECT statement, but you don't have to ...)Kristen |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-21 : 15:09:06
|
| Hi kristen,But they want to view the emp_id as well |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-22 : 02:12:37
|
| [code]SELECT emp_id, emp_name, emp_age, emp_address, date_assignedFROM tbl_emp_details EJOIN( SELECT emp_name, emp_age, emp_address, date_assigned FROM tbl_emp_details GROUP BY emp_name, emp_age, emp_address, date_assigned HAVING COUNT(*) > 1) T ON T.emp_name = E.emp_name AND T.emp_age = E.emp_age AND T.emp_address = E.emp_address AND T.date_assigned = E.date_assignedORDER BY emp_name, emp_age, emp_address, date_assigned[/code]Kristen |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
issue with duplicates