Flagging Unique ID's with SRN puzzle...

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 2005 Forums

Transact-SQL (2005)

Flagging Unique ID's with SRN puzzle...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-04 : 10:37:08

I have a bunch of SRN's and ID's which I need to flag.

Each unique ID can only be flagged once and this has to be flagged with a SRN

I find this hard to explain, but I'm basically trying to say if an ID
is flagged for the same SRN, then each unique ID (for the SRN grouping) has to be flagged with different a SRN.

Sample:
i.e:
SRN ID Flagged
2571 7754 x
2571 7755
2572 7754
2572 7755 x
2888 4632 x
2888 4990
2888 6344 x
2888 7006
2889 4632
2889 4990 x
2889 6344
2889 7006 x

Hope that makes sense...

I'm attempting to write some code to do this, but haven't had much luck so far.

Any suggestions welcome

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 10:41:34

I can see no rule on how to flag or not.
And I don't know why you are talking about UNIQUE IDs since they are not unique...

No, you're never too old to Yak'n'Roll if you're too young to die.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-04 : 10:48:35

I've edited my post and tried to explain it a bit better... hope that helps..

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-04 : 10:59:51

Does it have to be 'flagged' for another SRN or given assigned a whole new SRN altogether?

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-04 : 11:10:41

It just need to be marked with something (flagged) so that i know the ones which are not marked (flagged) can be removed from the population.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-04 : 11:27:27

Create table #SRN (SRN Int, ID Int)
Insert into #SRN
Select 2571,7754 Union all
Select 2571,7755 Union all
Select 2572,7754 Union all
Select 2572,7755 Union all
Select 2588,4632 Union all
Select 2588,4990 Union all
Select 2588,4632 Union all
Select 2588,6344

Select SRN, ID,
Case when a.rownum > 1 then 'X' else '' end as Flagged
from (Select SRN, ID, Row_Number() Over (Partition by ID Order by ID) as RowNum from #SRN) a

EDIT: Changed order by to ID.

Try this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-04 : 11:52:37

In 2888 and in 2889 are two flags - why?

No, you're never too old to Yak'n'Roll if you're too young to die.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-04 : 12:02:03

because they can have a different ID flagged

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-05 : 02:54:49

DP978 - Awesome solution. I was attempting with the Row_Number(), but totally forgot about the PARTITION BY!

Thanks!

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-05 : 03:12:42

Actually, after another check the code did not seem to work as I wanted it to:

i.e.

SRN _SRN DUPE row_number
2531047 165 1
2531048 165 Y 2
2531048 166 1
2531047 166 Y 2
166 2531047 Y 1
165 2531047 2
165 2531048 1
166 2531048 Y 2

I want it to be like this:
SRN ID DUPE ROW_NUMBER
2531047 165 1
2531048 165 Y 2
2531048 166 1
2531047 166 Y 2
166 2531047 Y 1
165 2531047 2
165 2531048 Y 1
166 2531048 2

--sorry I don't know how to format the above table...

i.e. If 166 has marked 2531047 as a dupe, then 165 must mark the other as dupe (as 166 is already marking a dupe - if an SRN is already marking a dupe, it cannot mark another dupe)

Hope this makes sense..

Thanks

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-12-05 : 13:25:15

your question is very interesting...

suppose 2 SRNs have only one ID and both Ids are same, whats the expected outcome?????????

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-12-06 : 01:25:36

your question is very interesting...

suppose 2 SRNs have only one ID and both Ids are same, whats the expected outcome?????????

I don't believe that will happen with the data I have.

But if it did, then I would only want one SRN and one ID (so the other is flagged as a dupe)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources