Comparing Records - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing Records

Author

Topic

golyath
Starting Member

21 Posts

Posted - 2007-10-24 : 08:34:12

Hi,

I have a table with 50 + columns....

I have a single record and i want to basically say are there any that are the same as this one.. but without using 50 where clauses...

Any Suggestions

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 08:41:30

Do you want to know if two or more of the columns have the same value in a same row?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 08:45:55

You mean some other record that is an exact replica over all 50+ columns?

E 12°55'05.25"
N 56°04'39.16"

golyath
Starting Member

21 Posts

Posted - 2007-10-24 : 08:47:25

Well, im not really sure how to go about it.

I basically select a row by a unique ID and i want to then say is there another row with the exact same data apart from the unique ID... Basically finding any duplicate rows

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 08:54:24

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Madhivanan

Failing to plan is Planning to fail

golyath
Starting Member

21 Posts

Posted - 2007-10-24 : 08:59:25

Thanks,

I will try this...

golyath
Starting Member

21 Posts

Posted - 2007-10-24 : 09:36:29

So here is what im trying to do:

SELECT Col1, Col2, Col3
FROM table AS table1
WHERE ID = '1'
UNION
SELECT Col1, Col2, Col3
FROM table AS table2

basically using the same table twice in the Union. The first select selects the specific record i want to compare. I was trying to use this Union to compare Col1, Col2 and Col3 of the Specific Record and see if there is another Record with the exact same data in these columns... I looked at the example in the link but im still alitlle confused as im not too experienced with SQL...

Am i heading in the right direction?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 10:09:07

SELECT t.*
FROM Table1 AS t
INNER JOIN (
SELECT CHECKSUM(Col1, Col2, Col2, ...) AS cs
FROM Table1
GROUP BY CHECKSUM(Col1, Col2, Col2, ...)
HAVING COUNT(*) > 1
) AS x ON x.cs = CHECKSUM(t.Col1, t.Col2, t.Col2, ...)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources