flag duplicates - 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

SQL Server 2000 Forums

SQL Server Development (2000)

flag duplicates

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-01-06 : 15:56:45

When I have duplicates, how do I mark the originals with a 1 and duplicates with a zero? Preferrably I would like the last duplicate in a series of duplicates to be 1. Thanks! - ray

desired results:
MyCode Original
A 1
B 0
B 1
C 0
C 0
C 1

CREATE TABLE [dbo].[MyTable1] (
[MyCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Original] [int] NULL
) ON [PRIMARY]
GO

INSERT mytable1 VALUES('A', null)
INSERT mytable1 VALUES('B', null)
INSERT mytable1 VALUES('B', null)
INSERT mytable1 VALUES('C', null)
INSERT mytable1 VALUES('C', null)
INSERT mytable1 VALUES('C', null)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-06 : 16:25:40

Let's back up a bit. Some of these items may appear to be overly pedantic or nit-picky but they should help going forward.

1) In the Relational Model every table has a Primary Key which is by definition unique. This prevents the situation that you have.

2) There is no "first" or "last" in a table or series. Don't think of a table as a spreadsheet with data neatly arranged in chronological order. Instead, think of a table as a bag of marbles. Note that the marbles are not in a particular order and if you pour the bag out, the marbles can come out in a different order every time.

3) If you want to impose an ordering to the data, it must be based on the data itself. since you don't have a unique way to identify a specific row, you can't do this.

How to correct this situation?

1)You could add an additional column to the table that was either unique by itself or was unique when combined with the MyCode column.

OR

2) You could remove the duplicate rows but it does sound like this is what you want to end up with.

HTH

=================================================================

A faith that cannot survive collision with the truth is not worth many regrets. -Arthur C Clarke, science fiction writer (1917- )

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-06 : 16:27:20

Nicely done Steve.

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-01-07 : 13:31:02

My mistake. I oversimpled my example. For each code, I want to flag one record with a 1 and all the rest w/ 0's. Thanks. - ray

desired results:
MyCode Original Address
A 1 A1
B 0 B2
B 1 B1
C 0 C3
C 0 C2
C 1 C1

CREATE TABLE [dbo].[MyTable1] (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[MyCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Original] [int] NULL ,
[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT mytable1 VALUES('A', null, 'A1')
INSERT mytable1 VALUES('B', null, 'B2')
INSERT mytable1 VALUES('B', null, 'B1')
INSERT mytable1 VALUES('C', null, 'C3')
INSERT mytable1 VALUES('C', null, 'C2')
INSERT mytable1 VALUES('C', null, 'C1')

VIG
Yak Posting Veteran

86 Posts

Posted - 2005-01-07 : 17:05:54

select t.mycode
,case when t2.address is null then 0 else 1 end Original
,t.address
from mytable1 t
left join
(select mycode,min(address) address from mytable1 group by mycode ) t2
on t.mycode=t2.mycode and t.address =t2.address

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-07 : 19:33:12

VIG,

Where is this "address" column coming from?

EHorn,

Thank-you.

HTH

=================================================================

A faith that cannot survive collision with the truth is not worth many regrets. -Arthur C Clarke, science fiction writer (1917- )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources