Re: How to do this in SQL?

From: Carlos Netto <Carlos.Netto_at_ibase.org.br>
Date: Sat, 20 Aug 94 22:26:00 -0300
Message-ID: <2aa.718.846%mpcbbs_at_ibase.org.br>

On 17 Aug 94 02:21pm, Stephen W. Evans wrote to ALL:

 SW> Newsgroup: comp.databases.oracle
 SW> Organization: MIT Plasma Fusion Center

 SW> What I need to do is search table foo which contains the following:

 SW> namefield
 SW> datafild
 SW> otherfield1
 SW> otherfield2
 SW> otherfieldn

 SW> and, for each unique namefield, find the highest value of datafield

 SW> and replace it with a constant. So far the closest I have been able  SW> to figure so far is:

[deleted lines]

update your_table
set datafild = 'your_constant'
where ( namefield, datafild ) in (

         select namefield, max( datafild )
         from   your_table
         group by namefield );

Remember that it can update one or *more* rows, if you have two or more rows with them same datafield max value.

Good luck,

---

| Carlos Augusto Leite Netto | Voice : +55.192.51.1153 |
| Software Design Informatica | Fax : +55.192.54.6518 |
| Oracle VAR | e-mail : carlos.netto%mpcbbs_at_ibase.org.br |
| Software Development & DBA | FidoNet: 4:801/31 (Carlos Netto) |

|---------------------------------------------------------------------------|


|  Softex 2000 member           | Brazil, Sao Paulo state, Campinas city    | 


=============================================================================

• Evaluation copy of Silver Xpress. Day # 57 --- via Silver Xpress V4.01P [NR]

  ---

User Name: Carlos Netto - ( %mpcbbs_at_ibase.org.br ) This message was processed by RAuucp from Merlin Systems Inc.

---

Received on Sun Aug 21 1994 - 03:26:00 CEST