[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