Query where one record's data is used as an expression 2

[rusty505]

I have a table with 3 columns. The first column is a unique customerID, where the second column is a group number. The third column is the date the customerID was created. What I want to do is create a query that returns the same fields but where the "Creation Date" for all customers is the latest date a customer was added to that customer's group.

for example:
1,5,Aug-06
2,5,Oct-06
3,6,Jun-06

would return

1,5,Oct-06
2,5,Oct-06
3,6,Jun-06

I have tried Subqueries but am having trouble pinning down the arguments! Am I going to have to incorporate additional code to keep track of the latest date for a certain group?
Thanks for any help...

 

[fredericofonseca]

assume the following table "tbl1"
id
gid
crtdate

select f1.id, f2.gid,f2.mcrtdate
from tbl1 f1
inner join
(select gid, max(crtdate) as mcrtdate
from tbl1
group by gid) z
on f1.gid = f2.gid

Untested, but you should get the idea.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[rusty505]

Yes that worked wonderfully (replacing "z" with f2).
This whole concept of assigning queries variables (f1, f2) was new to me. This will prove very helpful in many other places! Thanks for opening my eyes!

 

[dnoeth]

Another way using SQL:1999 OLAP functions:

select
id,
gid,
max(mcrtdate) over (partition by gid)
from tbl1

Dieter