Need ORDER BY in update

[RobHVB6Sql]

Update does not allow ORDER BY. How can I select the row with the largest Duration -an integer column?

SET ROWCOUNT = 1

UPDATE #contacts
SET Prov1ID = cd.IDCode
FROM tblGContactDetails cd
WHERE cd.contactID = #contacts.ContactID
  AND cd.Type = "Provider" 
  ORDER By cd.Duration DESC  -- cant do this!!
--  AND cd.Duration = (SELECT LARGEST(tblGContactDetails.Duration) )

Rob Hasard
(VB6 /SQL 7.0)

 

[swampBoogie]

UPDATE #contacts
SET Prov1ID = cd.IDCode
FROM tblGContactDetails cd
WHERE cd.contactID = #contacts.ContactID
  AND cd.Type = 'Provider'
  AND cd.Duration = 
   (SELECT max(Duration) 
      from tblGContactDetails)

 

[RobHVB6Sql]

Yep, worked it out.

UPDATE #contacts
SET Prov1ID = cd.IDCode 
FROM tblGContactDetails cd
WHERE cd.contactID = #contacts.ContactID
  AND cd.Type = 'Provider'

  AND cd.Duration = 
   (SELECT MAX(Duration) 
    FROM tblGContactDetails 
    WHERE contactID = #contacts.ContactID 
    AND Type = 'Provider')

Rob Hasard
(VB6 /SQL 7.0)

 

[donutman]

Know Irv Hasard?
-Karl

 

[RobHVB6Sql]

No, sorry I don't.
I'm in Perth - Western Australia.
Probably a long way from most of you guys and gals.

Rob Hasard
(VB6 /SQL 7.0)