Removing embedded duplicate rows
-
AHHHHAAAAAAAAAAAHH !!!!!!!!
select PictureName
From Pictures
group by PictureName
order by newid()
* Noel -
what if the PictureNames are 'camel 1', 'camel 2' etc...?!
**ASCII stupid question, get a stupid ANSI !!!** -
select PictureName
From Pictures
group by PictureName
order by newid()
seems to work....let me beat it to death, thanks
-
I just posted that After Bob mentioned that pictures numbers were his way of differentiating them
BTW did my solution to your trigger issue help ?
* Noel -
bob I do not think that
select PictureName
From Pictures
group by PictureName
order by newid()
Will work See sushilla's last post.
if object_ID('Tempdb..Pic') >0
drop table Pic
Create table Pic
(
PictureId int identity(1,1),
PictureName varchar(50)
)
insert into Pic Values('Dog1'
insert into Pic Values('Dog2')
insert into Pic Values('Dog3')
insert into Pic Values('Cat1')
insert into Pic Values('Cat2')
insert into Pic Values('Cat3')
select PictureName
From Pic
group by PictureName
order by newid()
/*
returns
Cat1
Cat2
Dog1
Cat3
Dog3
Dog2
*/
Mike
-
this is becoming funny...mike...see noel's last post...
I just posted that After Bob mentioned that pictures numbers were his way of differentiating them.........
**ASCII stupid question, get a stupid ANSI !!!** -
It is funny and I am still lost. Ok let me see, we have pictures in the database and some of these pictures are similar. Greyhounds, Boxers, pit bulls, chows, beagles, and bassets are all dogs.
1. The naming convention being used may or may not be constructed in a manner that will let us identify the type of picture it contains.
2. There are no columns that identify the type of picture or category that the item belongs to.
With this information we want to select a random sample from our database that contains only one of any given category.
Yes I have it.
Select u.CantGetThereFromHere
From Table u.
Where WeHaveNoClueAsToWhatIsStoredInThePictureOrWhatCatagorieItBelongTo
AND WhereWeCanOnlyHaveOnePictureFromACatagorie
I do think this will work
or perhaps I have been wroking to hard.Mike
{edited} changed
Select a. picture
From Table a
-
That sums is up... unless I missed something too
. -
well - bob day is in the process of "beating it to death"....let him tell us whether he's been flogging a dead horse or whether he's managed to draw some blood!
oops...there I go with my aggressive analogies again!
**ASCII stupid question, get a stupid ANSI !!!** -
Time for some more constructive comments... Can you clean up the data before it's too late?
-
oops...there I go with my aggressive analogies again!
you do have aggressive analogies perhaps the mouse in a lioness in disguise 
Mike
-
That's just part of her disguise.
-
there's no need to over-analyze this. This is the table I'm working with and this is what I need to do. I finish the project and then get paid.
The 'group by' post above seems to do the trick.
Some of you guys need to date more!
Thanks...anyway.
Bob
-
argh...that 'group by' thing doesn't work so hot. I might just fix this when I have the recordset in ASP....just skip a dup.
-
I don't wanna seem over analytic here, but if your table comprise such rows :
"caty 1" --typo
" cAt 2" --space before
"cats 3" --actually correct
"cats and dogs" --WTH??
and that you have no AnimalType column to differentiate them, then your screwed. You need to correct the data, normalize the table design and then the problem will solve itself (almost, that query is not that obvious to write). Otherwise you're stuck at guessing what the data means, and computers suck at this. With the panel of talented programmer you have on this thread, this post should have been closed in 10 minutes, and yet we're still talking about it. If you don't understand that this is a sign, then ??? good luck with that project.
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply