Update the max column SQL Help

[Peter Jameson]

Can any assist me with the syntax required for the following problem please.

Given the table and data below I want to update the row with the highest
key_field2 (within key_field1) to set key2_biggest to 'Y'. So rows
10,3 + 11,2 + 12,3 + 13,1 + 14,1 would be set to 'Y'.


Table: create table test_table
(key_field1 char(10),
key_field2 integer1,
key2_biggest char(1))


Data:
insert into test_table values('10',1,'')
insert into test_table values('10',2,'')
insert into test_table values('10',3,'')
insert into test_table values('11',1,'')
insert into test_table values('11',2,'')
insert into test_table values('12',1,'')
insert into test_table values('12',2,'')
insert into test_table values('12',3,'')
insert into test_table values('13',1,'')
insert into test_table values('14',1,'')

update test_table
set key2_biggest='N';

My attempt (that doesn't work!)...

update test_table a
set key2_biggest='Y'
where exists
(
select ''
from test_table b
where a.key_field1=b.key_field1 and
a.key_field2=b.key_field2
group by key_field1
having max(key_field2)
)

[Roy Hann]

Peter Jameson wrote:

> Can any assist me with the syntax required for the following problem please.
>
> Given the table and data below I want to update the row with the highest
> key_field2 (within key_field1) to set key2_biggest to 'Y'. So rows
> 10,3 + 11,2 + 12,3 + 13,1 + 14,1 would be set to 'Y'.
>
>
> Table: create table test_table
> (key_field1 char(10),
> key_field2 integer1,
> key2_biggest char(1))
>
>
> Data:
> insert into test_table values('10',1,'')
> insert into test_table values('10',2,'')
> insert into test_table values('10',3,'')
> insert into test_table values('11',1,'')
> insert into test_table values('11',2,'')
> insert into test_table values('12',1,'')
> insert into test_table values('12',2,'')
> insert into test_table values('12',3,'')
> insert into test_table values('13',1,'')
> insert into test_table values('14',1,'')

update test_table t1
set key2_biggest = 'Y'
where not exists ( select *
from test_table t2
where t2.key_field1 = t1.key_field1
and t2.key_field2 > t1.key_field2 )

--
Roy

UK Actian User Association Conference 2013 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.

[Karl Schendel]

On Mar 27, 2013, at 10:59 AM, Peter Jameson wrote:

> Can any assist me with the syntax required for the following problem please.
>
> Given the table and data below I want to update the row with the highest
> key_field2 (within key_field1) to set key2_biggest to 'Y'. So rows
> 10,3 + 11,2 + 12,3 + 13,1 + 14,1 would be set to 'Y'.
>
>
> Table: create table test_table
> (key_field1 char(10),
> key_field2 integer1,
> key2_biggest char(1))

update test_table a set key2_biggest = 'Y'
where not exists (select * from test_table b
where a.key_field1 = b.key_field1
and b.key_field2 > a.key_field2);

untested but it ought to work. :-)

Karl

[Peter Jameson]

> update test_table a set key2_biggest = 'Y'
> where not exists (select * from test_table b
> where a.key_field1 = b.key_field1
> and b.key_field2 > a.key_field2);
>
> untested but it ought to work. :-)
>
> Karl

Ah very clever, using not exists to exclude all those smaller keys. I
wouldn't have thought that one up myself.

[Karl Schendel]

Oddly enough, it followed directly from the problem statement
as I worded it to myself: "update the row where there is no
other row with a matching field1 and a larger field2."

I see that Roy came up with the same solution, and I
might venture to guess that his internal process was
similar.

Karl

[James K. Lowden]

On Wed, 27 Mar 2013 20:28:50 -0400
Karl Schendel <sche...@kbcomputer.com> wrote:

> > Ah very clever, using not exists to exclude all those smaller
> > keys. I wouldn't have thought that one up myself.
>

> Oddly enough, it followed directly from the problem statement
> as I worded it to myself: "update the row where there is no
> other row with a matching field1 and a larger field2."

Not odd in the least. Very early in the development of SQL, IIRC back
in System R, client feedback motivated the addition of quantifiers to
the language, which is how EXISTS came to appear in a language that
otherwise more nearly reflects relational algebra.

Codd thought the query language should be based in relational calculus,
too. So you're in good company.

--jkl

[Roy Hann]

Karl Schendel wrote:

>
> I see that Roy came up with the same solution, and I
> might venture to guess that his internal process was
> similar.

You venture correctly. I also fixed the proposed query but thought it
looked horrible and mysterious:

update test_table t1
set key2_biggest = 'Y'
where exists (
select key_field1
from test_table t2
group by key_field1
having t1.key_field2 = max(t2.key_field2)
and t1.key_field1 = t2.key_field1 )

Maybe it's a matter of taste.

[Paul White]

But it matches the wording of the requirement..