Marking (Not just Finding) Duplicate Values ......

microsoft.public.sqlserver.programming

1 Sep 2006 5:03 PM

sloan

Below is a sample of what I'm trying to do.
Let's say I have table (fakeRealTable in my sample below)
It has duplicate rows, the data is the same EXCEPT for the primary key
(entryid in my sample)

I want to create another (@variable or #temp table) which contains all the
values from @fakeRealTable,
however, I want to add a column (called oneOccurenceOfThisCombo) which is a
bit.
I know how to use distinct to get a list of distinct values.
I want to actually mark them ... and set the oneOccurenceOfThisCombo to the
value of 1 for one of the distinct combo's, but for the rest of them
I want to have the oneOccurenceOfThisCombo be a value of 0.

Describing it further there is a sampling of the tsql below .

11, red, car
12, red, car
13, red, car

I have 3 rows. Each with a unique primary key. (11, 12, 13). However,
besides the primary key, "red" and "car" are 1 distinct value.
So I'd like to have another table with

11,red,car,1
12,red,car,0
13,red,car,0

I don't care which of the 3 rows gets the "1" value. Just as long as only
one row (for the distinct "red","car") has a 1 value, and every other
"red","car" has a 0.
Saying if a different way, the result data could look like this:
11,red,car,0
12,red,car,1
13,red,car,0

OR

11,red,car,0
12,red,car,0
13,red,car,1

Either of these 3 combo's is ok. Just as long as only one "red","car" is
marked with a 1 value on the last column.

Only knowing that "red","car" is the distinct value(s) will not help me.
I need a final table with all 3 original rows, just with the extra bit
column at the end.

Here is a coded up sample.

-- select * from @tryingToGetHere
When I run this query below, I"m showing you what I want my end results will
be.
I think how I've asked is clear.

I appreciate any help.

I know I can use a CURSOR, what I'm really looking for is a NON CURSOR, or
non while loop solution.

I've spent +10 hours on it, thus why I'm asking the group.

Thanks!

-- START Tsql code

set nocount on

declare @fakeRealTable table ( entryid int , color varchar(12) , toy
varchar(12) )

insert into @fakeRealTable (entryid , color , toy ) values ( 11 , 'red' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 12 , 'red' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 13 , 'red' ,
'car')

insert into @fakeRealTable (entryid , color , toy ) values ( 21 , 'red' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 22 , 'red' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 23 , 'red' ,
'plane')

insert into @fakeRealTable (entryid , color , toy ) values ( 14 , 'green' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 15 , 'green' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 16 , 'green' ,
'car')

insert into @fakeRealTable (entryid , color , toy ) values ( 24 , 'green' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 25 , 'green' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 26 , 'green' ,
'plane')

insert into @fakeRealTable (entryid , color , toy ) values ( 17 , 'blue' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 18 , 'blue' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 27 , 'blue' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 28 , 'blue' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 19 , 'blue' ,
'boat')

select * from @fakeRealTable

print 'Here are the distinct combinations'
select distinct color , toy from @fakeRealTable order by color , toy

declare @tryingToGetHere table ( entryid int , color varchar(12) , toy
varchar(12) , oneOccurenceOfThisCombo bit )

insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 11 , 'red' , 'car' , 1)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 12 , 'red' , 'car' , 0)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 13 , 'red' , 'car', 0)

insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 21 , 'red' , 'plane', 1)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 22 , 'red' , 'plane', 0)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 23 , 'red' , 'plane', 0)

insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 14 , 'green' , 'car' , 1)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 15 , 'green' , 'car', 0)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 16 , 'green' , 'car', 0)

insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 24 , 'green' , 'plane' , 1)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 25 , 'green' , 'plane', 0)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 26 , 'green' , 'plane', 0)

insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 17 , 'blue' , 'car' , 1)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 18 , 'blue' , 'car' , 0)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 27 , 'blue' , 'plane' , 1)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 28 , 'blue' , 'plane' , 0)
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo) values ( 19 , 'blue' , 'boat' , 1)

select * from @tryingToGetHere order by oneOccurenceOfThisCombo desc , color
, toy

Delete from @tryingToGetHere

--The next lines will naturally produce an error, but this is where I'm
looking for the magic bullet query
insert into @tryingToGetHere (entryid , color , toy ,
oneOccurenceOfThisCombo)
Select (magic stuff here) from @fakeRealTable --Which gives me the same
results as the "select * from @tryingToGetHere" above

1 Sep 2006 5:13 PM

Barry

Hope this helps

Barry

SQL Code:

declare @fakeRealTable table ( entryid int , color varchar(12) , toy
varchar(12), Duplicate char(1) default 'N' )

insert into @fakeRealTable (entryid , color , toy ) values ( 11 , 'red'
,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 12 , 'red'
,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 13 , 'red'
,
'car')

insert into @fakeRealTable (entryid , color , toy ) values ( 21 , 'red'
,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 22 , 'red'
,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 23 , 'red'
,
'plane')

insert into @fakeRealTable (entryid , color , toy ) values ( 14 ,
'green' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 15 ,
'green' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 16 ,
'green' ,
'car')

insert into @fakeRealTable (entryid , color , toy ) values ( 24 ,
'green' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 25 ,
'green' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 26 ,
'green' ,
'plane')

insert into @fakeRealTable (entryid , color , toy ) values ( 17 ,
'blue' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 18 ,
'blue' ,
'car')
insert into @fakeRealTable (entryid , color , toy ) values ( 27 ,
'blue' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 28 ,
'blue' ,
'plane')
insert into @fakeRealTable (entryid , color , toy ) values ( 19 ,
'blue' ,
'boat')

Update @fakeRealTable
Set Duplicate = 'Y'
Where EntryID In
(Select Max(EntryID) From @fakeRealTable
Group By Color, Toy
Having Count(*) > 1)

select * from @fakeRealTable

1 Sep 2006 5:17 PM

David Portas

sloan wrote:
Show quote

> Below is a sample of what I'm trying to do.
> Let's say I have table (fakeRealTable in my sample below)
> It has duplicate rows, the data is the same EXCEPT for the primary key
> (entryid in my sample)
>
> I want to create another (@variable or #temp table) which contains all the
> values from @fakeRealTable,
> however, I want to add a column (called oneOccurenceOfThisCombo) which is a
> bit.
> I know how to use distinct to get a list of distinct values.
> I want to actually mark them ... and set the oneOccurenceOfThisCombo to the
> value of 1 for one of the distinct combo's, but for the rest of them
> I want to have the oneOccurenceOfThisCombo be a value of 0.
>

[snip]

Try this:

UPDATE T
SET oneOccurenceOfThisCombo = 1
FROM @tryingToGetHere AS T
WHERE entryid =
(SELECT MIN(entryid)
FROM @tryingToGetHere AS R
WHERE R.color = T.color
AND R.toy = T.toy);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

1 Sep 2006 5:21 PM

sloan

Barry.

Thanks. Your solution does help for my immediate need. Luckily, I have
"int" as the primary key.

For academic and future reasons ( as I flip flop between projects that have
ints as primary keys ... and guid's as primary keys)
Here is the same problem, but using uniqueidentifiers.

Like I said , your solution helps my immediate problem, so thanks.

But here is the other twist on things:

set nocount on

declare @fakeRealTable table ( uuid uniqueidentifier , color varchar(12) ,
toy varchar(12) )

insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red' ,
'car')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red' ,
'car')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red' ,
'car')

insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red' ,
'plane')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red' ,
'plane')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red' ,
'plane')

insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'green'
, 'car')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'green'
, 'car')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'green'
, 'car')

insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'green'
, 'plane')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'green'
, 'plane')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'green'
, 'plane')

insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'blue'
, 'car')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'blue'
, 'car')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'blue'
, 'plane')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'blue'
, 'plane')
insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'blue'
, 'boat')

select * from @fakeRealTable

print 'Here are the distinct combinations'
select distinct color , toy from @fakeRealTable order by color , toy

declare @tryingToGetHere table ( uuid uniqueidentifier , color varchar(12) ,
toy varchar(12) , oneOccurenceOfThisCombo bit )

insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'red' , 'car' , 1) -- I know these are not the same
guids from above.. but thats not the point.
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'red' , 'car' , 0)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'red' , 'car', 0)

insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'red' , 'plane', 1)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'red' , 'plane', 0)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'red' , 'plane', 0)

insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'green' , 'car' , 1)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'green' , 'car', 0)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'green' , 'car', 0)

insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'green' , 'plane' , 1)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'green' , 'plane', 0)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'green' , 'plane', 0)

insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'blue' , 'car' , 1)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'blue' , 'car' , 0)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'blue' , 'plane' , 1)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'blue' , 'plane' , 0)
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
values ( NEWID() , 'blue' , 'boat' , 1)

select * from @tryingToGetHere order by oneOccurenceOfThisCombo desc , color
, toy

Delete from @tryingToGetHere

--this is bad syntax of course ...
insert into @tryingToGetHere (uuid , color , toy , oneOccurenceOfThisCombo)
Select (magic stuff here) from @fakeRealTable --Which gives me the same
results as the "select * from @tryingToGetHere" above

1 Sep 2006 5:32 PM

Barry

In that case you would use an Update Statement similar to the one that
David provided for you.

Update a
Set Duplicate = 'Y'

>From @fakeRealTable a
Where uuid =

(Select Top 1 (b.uuid) From @fakeRealTable b
Where a.Color = b.Color
And a.Toy = b.Toy
Order By b.uuid)

HTH

Barry

1 Sep 2006 7:22 PM

Arnie Rowland

Hi,

Since you are concerned, academically, I thought these articles may be of
some utility.

GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599

GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx

GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1

GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"sloan" <sl***@ipass.net> wrote in message
news:%23Q6fNtezGHA.4580@TK2MSFTNGP05.phx.gbl...
>
> Barry.
>
> Thanks. Your solution does help for my immediate need. Luckily, I have
> "int" as the primary key.
>
> For academic and future reasons ( as I flip flop between projects that
> have
> ints as primary keys ... and guid's as primary keys)
> Here is the same problem, but using uniqueidentifiers.
>
>
> Like I said , your solution helps my immediate problem, so thanks.
>
> But here is the other twist on things:
>
>
>
>
>
>
>
>
> set nocount on
>
> declare @fakeRealTable table ( uuid uniqueidentifier , color varchar(12) ,
> toy varchar(12) )
>
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red'
> ,
> 'car')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red'
> ,
> 'car')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red'
> ,
> 'car')
>
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red'
> ,
> 'plane')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red'
> ,
> 'plane')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() , 'red'
> ,
> 'plane')
>
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'green'
> , 'car')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'green'
> , 'car')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'green'
> , 'car')
>
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'green'
> , 'plane')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'green'
> , 'plane')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'green'
> , 'plane')
>
>
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'blue'
> , 'car')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'blue'
> , 'car')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'blue'
> , 'plane')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'blue'
> , 'plane')
> insert into @fakeRealTable (uuid , color , toy ) values ( NEWID() ,
> 'blue'
> , 'boat')
>
>
> select * from @fakeRealTable
>
>
> print 'Here are the distinct combinations'
> select distinct color , toy from @fakeRealTable order by color , toy
>
>
> declare @tryingToGetHere table ( uuid uniqueidentifier , color varchar(12)
> ,
> toy varchar(12) , oneOccurenceOfThisCombo bit )
>
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'red' , 'car' , 1) -- I know these are not the same
> guids from above.. but thats not the point.
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'red' , 'car' , 0)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'red' , 'car', 0)
>
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'red' , 'plane', 1)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'red' , 'plane', 0)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'red' , 'plane', 0)
>
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'green' , 'car' , 1)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'green' , 'car', 0)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'green' , 'car', 0)
>
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'green' , 'plane' , 1)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'green' , 'plane', 0)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'green' , 'plane', 0)
>
>
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'blue' , 'car' , 1)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'blue' , 'car' , 0)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'blue' , 'plane' , 1)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'blue' , 'plane' , 0)
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> values ( NEWID() , 'blue' , 'boat' , 1)
>
> select * from @tryingToGetHere order by oneOccurenceOfThisCombo desc ,
> color
> , toy
>
>
> Delete from @tryingToGetHere
>
> --this is bad syntax of course ...
> insert into @tryingToGetHere (uuid , color , toy ,
> oneOccurenceOfThisCombo)
> Select (magic stuff here) from @fakeRealTable --Which gives me the same
> results as the "select * from @tryingToGetHere" above
>
>
>

1 Sep 2006 5:50 PM

sloan

Thanks to both of you.

I finally got it working with

Delete from @tryingToGetHere--this is from the orginal post... aka, where I
am putting the code below in at:

INSERT INTO @tryingToGetHere ( uuid , color , toy ,
oneOccurenceOfThisCombo )
select uuid , color , toy , 0 from @fakeRealTable

Update @tryingToGetHere
Set oneOccurenceOfThisCombo = 1
From @fakeRealTable frtOne
Where frtOne.uuid =
(Select Top 1 (frtTwo.uuid) From @fakeRealTable frtTwo
Where frtOne.Color = frtTwo.Color
And frtOne.Toy = frtTwo.Toy )

Show quote

"sloan" <sl***@ipass.net> wrote in message
news:u%23M3FjezGHA.2196@TK2MSFTNGP03.phx.gbl...
>
> Below is a sample of what I'm trying to do.
> Let's say I have table (fakeRealTable in my sample below)
> It has duplicate rows, the data is the same EXCEPT for the primary key
> (entryid in my sample)
>
> I want to create another (@variable or #temp table) which contains all the
> values from @fakeRealTable,
> however, I want to add a column (called oneOccurenceOfThisCombo) which is
a
> bit.
> I know how to use distinct to get a list of distinct values.
> I want to actually mark them ... and set the oneOccurenceOfThisCombo to
the
> value of 1 for one of the distinct combo's, but for the rest of them
> I want to have the oneOccurenceOfThisCombo be a value of 0.
>
> Describing it further there is a sampling of the tsql below .
>
> 11, red, car
> 12, red, car
> 13, red, car
>
> I have 3 rows. Each with a unique primary key. (11, 12, 13). However,
> besides the primary key, "red" and "car" are 1 distinct value.
> So I'd like to have another table with
>
> 11,red,car,1
> 12,red,car,0
> 13,red,car,0
>
> I don't care which of the 3 rows gets the "1" value. Just as long as only
> one row (for the distinct "red","car") has a 1 value, and every other
> "red","car" has a 0.
> Saying if a different way, the result data could look like this:
> 11,red,car,0
> 12,red,car,1
> 13,red,car,0
>
> OR
>
> 11,red,car,0
> 12,red,car,0
> 13,red,car,1
>
> Either of these 3 combo's is ok. Just as long as only one "red","car" is
> marked with a 1 value on the last column.
>
> Only knowing that "red","car" is the distinct value(s) will not help me.
> I need a final table with all 3 original rows, just with the extra bit
> column at the end.
>
> Here is a coded up sample.
>
> -- select * from @tryingToGetHere
> When I run this query below, I"m showing you what I want my end results
will
> be.
> I think how I've asked is clear.
>
> I appreciate any help.
>
> I know I can use a CURSOR, what I'm really looking for is a NON CURSOR, or
> non while loop solution.
>
> I've spent +10 hours on it, thus why I'm asking the group.
>
> Thanks!
>
>
> -- START Tsql code
>
> set nocount on
>
> declare @fakeRealTable table ( entryid int , color varchar(12) , toy
> varchar(12) )
>
> insert into @fakeRealTable (entryid , color , toy ) values ( 11 , 'red' ,
> 'car')
> insert into @fakeRealTable (entryid , color , toy ) values ( 12 , 'red' ,
> 'car')
> insert into @fakeRealTable (entryid , color , toy ) values ( 13 , 'red' ,
> 'car')
>
> insert into @fakeRealTable (entryid , color , toy ) values ( 21 , 'red' ,
> 'plane')
> insert into @fakeRealTable (entryid , color , toy ) values ( 22 , 'red' ,
> 'plane')
> insert into @fakeRealTable (entryid , color , toy ) values ( 23 , 'red' ,
> 'plane')
>
> insert into @fakeRealTable (entryid , color , toy ) values ( 14 , 'green'
,
> 'car')
> insert into @fakeRealTable (entryid , color , toy ) values ( 15 , 'green'
,
> 'car')
> insert into @fakeRealTable (entryid , color , toy ) values ( 16 , 'green'
,
> 'car')
>
> insert into @fakeRealTable (entryid , color , toy ) values ( 24 , 'green'
,
> 'plane')
> insert into @fakeRealTable (entryid , color , toy ) values ( 25 , 'green'
,
> 'plane')
> insert into @fakeRealTable (entryid , color , toy ) values ( 26 , 'green'
,
> 'plane')
>
>
> insert into @fakeRealTable (entryid , color , toy ) values ( 17 , 'blue' ,
> 'car')
> insert into @fakeRealTable (entryid , color , toy ) values ( 18 , 'blue' ,
> 'car')
> insert into @fakeRealTable (entryid , color , toy ) values ( 27 , 'blue' ,
> 'plane')
> insert into @fakeRealTable (entryid , color , toy ) values ( 28 , 'blue' ,
> 'plane')
> insert into @fakeRealTable (entryid , color , toy ) values ( 19 , 'blue' ,
> 'boat')
>
>
> select * from @fakeRealTable
>
>
> print 'Here are the distinct combinations'
> select distinct color , toy from @fakeRealTable order by color , toy
>
>
> declare @tryingToGetHere table ( entryid int , color varchar(12) , toy
> varchar(12) , oneOccurenceOfThisCombo bit )
>
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 11 , 'red' , 'car' , 1)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 12 , 'red' , 'car' , 0)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 13 , 'red' , 'car', 0)
>
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 21 , 'red' , 'plane', 1)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 22 , 'red' , 'plane', 0)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 23 , 'red' , 'plane', 0)
>
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 14 , 'green' , 'car' , 1)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 15 , 'green' , 'car', 0)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 16 , 'green' , 'car', 0)
>
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 24 , 'green' , 'plane' , 1)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 25 , 'green' , 'plane', 0)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 26 , 'green' , 'plane', 0)
>
>
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 17 , 'blue' , 'car' , 1)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 18 , 'blue' , 'car' , 0)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 27 , 'blue' , 'plane' , 1)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 28 , 'blue' , 'plane' , 0)
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo) values ( 19 , 'blue' , 'boat' , 1)
>
> select * from @tryingToGetHere order by oneOccurenceOfThisCombo desc ,
color
> , toy
>
>
> Delete from @tryingToGetHere
>
> --The next lines will naturally produce an error, but this is where I'm
> looking for the magic bullet query
> insert into @tryingToGetHere (entryid , color , toy ,
> oneOccurenceOfThisCombo)
> Select (magic stuff here) from @fakeRealTable --Which gives me the same
> results as the "select * from @tryingToGetHere" above
>
>