Make change to last record in a group of records

[ozlink]

Hi All

Hopefully someone can help with the following problem I have.
I have a table which has a list of records and I need to make a change
to the last record in a group. ie every record has a value in field B
of "abc" however in the last record where field A's value is the same
the value in field B needs to be "xyz"



For example

existing data may look as follows


field A Field B

10034 abc
10034 abc
10034 abc
11245 abc
11245 abc
11245 abc
11245 abc
9564 abc
9564 abc
9564 abc

What I want it to end up looking like is the following

field A Field B

10034 abc
10034 abc
10034 xyz
11245 abc
11245 abc
11245 abc
11245 xyz
9564 abc
9564 abc
9564 xyz


Thanks in Adavance
David

 

[ChrisM]

Hi,

I assume that there are othe fields in this table, (or what is the point in
the duplicate entries?)
On this basis, and for this example, I'm going to use an 'Number' field
called tablePos which just runs from 1 to .. from the top to the bottom of
the table:

ie

FIELDA FIELDB tablePOS
10034 abc 1
10034 abc 2
10034 abc 3
11245 abc 1
11245 abc 2
11245 abc 3
11245 abc 4
9564 abc 1
9564 abc 2
9564 abc 3



UPDATE YourTable as outerTab
set FieldB = "XYZ"
WHERE NOT EXISTS
(
SELECT * FROM YourTable as innerTab
WHERE innerTab.FieldA = outerTab.FieldA
AND innerTab.tablePos > outerTab.tablePos
)



Cheers,

ChrisM