[thelist] SQL to return one result for duplicate values

[rudy]

> 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 found a page that suggests solving a similar problem with:
>
> SELECT Count(*), word_value
> GROUP BY 1 having Count(*) > 1

yikes, that gives a syntax error, right?

> Seems what I need is:
>
> SELECT Count(word_value), word_value
> GROUP BY 1 having Count(word_value) = 1

i'm having a little trouble figuring out whether you want the ones that are
duplicated (from your subject line) or the ones that aren't (from the
above, count=1)

if you want the word_values that exist in the table more than once, use

    select word_value
         from thetable
    group by word_value
        having count(*) > 1

if you want the ones that exist only once, use  count(*) = 1


> 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?

> ... 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)

"every distinct value for a column plus everything else for that column"
doesn't make sense -- there wouldn't be anything else besides everything

let me know if the above helped, or how you want it extended...

for example, it's often not enough just to know that a table has duplicate
entries, often you will want to delete all but one of the duplicates

Kayode yusuf posted a query the other day that i wanted to comment on --

  delete from tblFOO a
   where ID = ( Select MAX(ID)
             From   tblFOO b
            WHERE  b.Name = a.Name
            AND    b.Category = a.Category
            AND    b.LastDate = a.LastDate)

without having tested this, i'm suspicious that it might not also delete si
ngletons, which is probably *not* what you want

did you want to delete duplicates, erik?

rudy