Removing embedded duplicate rows
-
I have a table that has a field that contains similar data. i.e.
cat 1
cat 2
dog 1
dog 2
I want to return only one entry, distinct doesn't work here. Any suggestions.
Thanks,
Bob Day
-
You will need to be a bit more specific because distinct does just that!!
select distinct Fld from table
but without more specifics can't go further!
* Noel -
I'm using order by newID() to select a random list from this table, however I don't want 2 of any particular occurance (ie..dog 1, dog 2)...if I have dog 2 I don't want dog 1 in the result set, DISTINCT to my limited knowledge works on the whole field....not a substring of it...right?
-
Can you post some sample data and your expected results ?
* Noel -
Your DDL would be helpful here....
Mathew J Kulangara
sqladventures.blogspot.com -
select description,
from FEATS_PictureTEST order by newid()
DESCRIPTION Field
bathing suit 1
thirty two 1
throwing 1
rectangle 2
sweater 2
forty eight 1
camel 2
shirt 1
thirty five 1
fourteen 2
camel 1
I don't want the camel 1 row at the end.
-Bob
-
Bob..
Does 'Camel 1' comprise one field or 2 ??
did you mean
select description, {id}
from FEATS_PictureTEST order by newid() ??
Mathew J Kulangara
sqladventures.blogspot.com -
could you have duplicates ?!
camel 1
camel 1
camel 1.........???
does the description field always end in numbers ?
is it always 1 and 2 or could they go up to any number ?
**ASCII stupid question, get a stupid ANSI !!!** -
For preference I would have some sort of category column which categorised the record rather than use a compound field, but I was wondering if you could use SOUNDEX() in some way, since SOUNDEX('Camel 1') and SOUNDEX('Camel 2') or SOUNDEX('Camel 222') all return the same value. It seems to ignore the numbers at the end.
David
If it ain't broke, don't fix it...
-
Or you could write a little UDF something like (assuming description is VARCHAR(40))
CREATE FUNCTION udf_StripNumber (@strTestString VARCHAR(40))
RETURNS VARCHAR(40) AS
BEGIN
WHILE (LEN(@strTestString)>0 AND RIGHT(@strTestString,1) IN ('0','1','2','3','4','5','6','7','8','9'))
SET @strTestString=LEFT(@strTestString,LEN(@strTestString)-1)
RETURN (RTRIM(@strTestString))
END
For some reason I don't understand I cant seem to use ISNUMERIC() in this function to test the rightmost character in the test string.
This removes the numbers from the end of the Description field, but still leaves you with the problem of writing a query to select a list of descriptions which doesn't include more than one Camel (or something sounding like a camel).
Does your table use the description as a key field?
David
If it ain't broke, don't fix it...
-
select isnumeric(right('camel 1', 1))
select isnumeric(left(reverse('camel 1'), 1))...
these seem to work fine...didn't test with variables though...
**ASCII stupid question, get a stupid ANSI !!!** -
camel 1
camel 2
are just data in a varchar field, nothing special....here's the deal...I use ORDER BY NEWID() to get a random sample of these pictures(that's what they are)...sometimes the other same picture (ie camel 1) gets selected too and that's not good for the game I'm writing...
-
I think I might have to write a stored procedure to create a cursor of the random result set then somehow remove the dups from top down
-
Bob how do you know that camel 1 and camel 2 are dups. Is it possible to have a camel 1 and a camel 2 that are not dups.
Mike
-
no,no,no.....these are pictures...2 pictures of a camel, dog, cat...anything....I need to randomly select pictures but i don't want 2 of the same picture...sorry for the confusion
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply