[thelist] SQL to return one result for duplicate values
Erik Mattheis
gozz at gozz.com
Mon Apr 29 17:50:01 CDT 2002
I'm using SQL server 7 and am having trouble coming up with a query
that returns a list of distinct values for a particular field.
The table:
word_id
word_type
word_value
word_date
flag
I found a page that suggests solving a similar problem with:
SELECT Count(*), word_value
GROUP BY 1 having Count(*) > 1
Seems what I need is:
SELECT Count(word_value), word_value
GROUP BY 1 having Count(word_value) = 1
Every way I've tried this gives an error.
But I also need to group by word_type and the word_id for one of the
duplicate values must be returned.
I've also tried comparing values of results from the same table using
two aliases ... and I can get it to nicely come up with _only_ values
that are duplicates, but what I really want is one query that will
return a list of every distinct value for a particular column, plus
everything else for that column. I hope I explained the situation
sufficiently ... if not here are the two things I'm using ...
To find attribute_value1 that only occur once:
SELECT DISTINCT attribute_values.attribute_value1,
attribute_values.attribute_value_id, attributes.attribute_name,
attribute_values.attribute_value_date
FROM attributes, attribute_values
WHERE attribute_values.flag = #variables.flag# AND
attribute_values.attribute_value1 <> '' AND
attribute_values.attribute_value_id NOT IN (SELECT
av1.attribute_value_id
FROM attribute_values av1, attribute_values av2
WHERE av1.attribute_value1 = av2.attribute_value1 AND
av1.attribute_value_id <>
av2.attribute_value_id)
ORDER BY
attributes.attribute_name,attribute_values.attribute_value_date DESC
To find attribute_value1 that occur more than once:
SELECT DISTINCT av1.attribute_value1, av1.attribute_value_id,
attributes.attribute_name, av1.attribute_value_date
FROM attribute_values av1, attribute_values av2, attributes
WHERE av1.attribute_value1 = av2.attribute_value1 AND
av1.attribute_value_id <> av2.attribute_value_id
ORDER BY av1.attribute_value1
--
__________________________________________
- Erik Mattheis
(612) 377 2272
http://goZz.com/
__________________________________________
More information about the thelist
mailing list