[thelist] SQL to return one result for duplicate values

Erik Mattheis gozz at gozz.com
Mon Apr 29 18:42:01 CDT 2002


At 7:16 PM -0400 4/29/02, rudy wrote:

>  > 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.
>
>hi erik
>
>the general form is SELECT DISTINCT(column)

I thought DISTINCT caused the result not to include duplicate rows
... and that's the way it's working when I say

SELECT DISTINCT word_value, word_id

or

SELECT DISTINCT(word_value), word_id

>  > But I also need to group by word_type and the word_id
>>  for one of the duplicate values must be returned.
>
>not really sure what you want -- can you rephrase please?

Here is some example data:

word_id  word_value
1        butter
2        guns
3        guns
4        guns
5        gas
6        butter

I would like to query this table and come up with the following result:

word_id  word_value
1        butter
2        guns
5        gas

It doesn't matter _which_ word_id it returns for guns or butter, just
that it only returns one row with guns in it and one row with butter
in it and one row with gas in it.

>  > ... 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
>
>heh, you just made it worse   ;o)

Indeed I did, wrote "column" when I meant "row" ... I meant to say,
"What I want is a query that will return once instance of duplicate
values for a particular column, plus everything else in one of the
rows that the duplicate value exists in - without ignoring rows which
contain values for that column which are _not_ duplicated."
--

__________________________________________
- Erik Mattheis

(612) 377 2272
http://goZz.com/

__________________________________________



More information about the thelist mailing list