OT Need some help with a SQL select statement
Thread Starter
AudiWorld Super User
Joined: Jul 2001
Posts: 14,487
Likes: 0
From: Orlando, FL
OT Need some help with a SQL select statement
This is for straight transact-SQL statement to be run in query analyzer against SQL Server 7.0
I have afield that holds unique #'s in a table. When you do a select on this field for a particular number, it should return ONLY 1 row all the time.
I've got duplicate numbers right now, so I need to find all the rows where these dupes occur, but I only want to return these rows. So how can i design my select statement so it will only show me all the duplicate instances rows?
I have afield that holds unique #'s in a table. When you do a select on this field for a particular number, it should return ONLY 1 row all the time.
I've got duplicate numbers right now, so I need to find all the rows where these dupes occur, but I only want to return these rows. So how can i design my select statement so it will only show me all the duplicate instances rows?
Elder Member
Joined: Apr 2001
Posts: 26,881
Likes: 0
in cases of dupe numbers...will all the data be identical??
if so, then do this:
SELECT DISTINCT * FROM tb_tablename
Then, take those query results and insert them into a new table....I'm not sure how to write that part of it.
If the records containing dupe ID's have different info in them (but the same ID)...then you're kinda screwed. there is no way to automate which record is valid, unless you have a date field that tells when the right or wrong record was put in, or something similar, that you could query, to weed out the wrong fields.
SELECT DISTINCT * FROM tb_tablename
Then, take those query results and insert them into a new table....I'm not sure how to write that part of it.
If the records containing dupe ID's have different info in them (but the same ID)...then you're kinda screwed. there is no way to automate which record is valid, unless you have a date field that tells when the right or wrong record was put in, or something similar, that you could query, to weed out the wrong fields.
Member
Joined: Mar 2000
Posts: 3,348
Likes: 0
Re: OT Need some help with a SQL select statement
find the dup keys and insert into a temp table. and then do a join to the temp table
select keyfield
into temptable
from table
group by keyfield
having count(*) > 1
select t.* from table t, temptable r
where t.keyfield = r.keyfield
drop table temptable
select keyfield
into temptable
from table
group by keyfield
having count(*) > 1
select t.* from table t, temptable r
where t.keyfield = r.keyfield
drop table temptable
Trending Topics
Senior Member
Joined: Jun 2000
Posts: 7,903
Likes: 0
yes but it can still work in this case as long as there is another field to check against that
wouldn't be the same in for the dup. keys ie..
select table1.*
from thetable table1, thetable table2
where table1.field = table2.field AND table1.field2 <> table2.field2
;
select table1.*
from thetable table1, thetable table2
where table1.field = table2.field AND table1.field2 <> table2.field2
;
AudiWorld Stories
Bringing Audi to Life for Audi Fans
2027 Audi Q7 and SQ7: Audi Upgraded EVERYTHING!
Michael S. Palmer
Audi Unveils Absurdly Cool New Supercar: 10 Things You Need to Know!
Verdad Gallardo
The Highs & Lows of Every Audi C-Class Generation
Joe Kucinski
Top 10 Most Expensive Audis Ever Sold on Bring-A-Trailer
Brett Foote
10 Audi Features & Options We Miss the Most!
Joe Kucinski
Audi Recreates Crazy-Looking Speed Record Breaker From 1935
Verdad Gallardo
Coachbuilder Recreates the 1995 Audi TTS Concept
Verdad Gallardo
Every Audi V10 Car Ranked!
Joe Kucinski
