We help IT Professionals succeed at work.

how to select distict rows

Rozamunda
Rozamunda asked
on
I would like  my select to return only single row, Since fld3,fld4 can repeat
update file1 set fld1 = (select coalesce(sum(fld2,0))             
  from file2 
 group BY fld3, fld4

Open in new window

Comment
Watch Question

Dave FordSoftware Developer / Database Administrator

Commented:

I suppose adding a "fetch first 1 row only" clause would do it:

update file1
   set fld1 = (
       select coalesce(sum(fld2,0))
         from file2 
        group BY fld3, fld4 
       fetch first 1 row only
   )

HTH,
DaveSlash

Open in new window

How can they repeat after a GROUP BY? Your closing parenthesis needs to be moved after fld4 to give a more rational structure.

But it doesn't seem that the problem is that fld3 and fld4 repeat. It seems more that there are multiple groups of unique fld3 and fld4 combinations. You can't update a single column with values from multiple groups. You either need to limit the SELECT to a specific (fld3,fld4) group, perhaps with a WHERE clause, or to get rid of the GROUP BY completely and use the SUM() of all rows.

What are you trying to accomplish?

Tom

Author

Commented:
ok, tliotta you are right , I just don't need the group by, my bad

Author

Commented:
the error I was getting is that more than one row is selected