Problem with correlated subquery using EXISTS

[barry.marcus]

I have a table, named MyTable, created with the following statement:

create table MyTable (pid varchar(8), rdate varchar(8), info varchar(255));

I inserted the following rows:
'123', '20060101', '20060101'
'123', '20050101', '20050101'
'987', '20060101', '20060101'

I want to extract the one row for each pid that has the greatest value in the rdate column. (For this set of test data, that would be the first and third rows. Row 2 is to be skipped because its pid is the same as that of row 1, but the rdate value is less than that of row 1.)

I thought the following query might work, but it does not:

select * from MyTable A where exists (select pid, max(rdate) from MyTable B where A.pid = B.pid group by B.pid);

Any help would be appreciated.

Thanks

[John]

There are a couple of approaches you can take. The one you have is not comparing against max(rdate) anywhere.

A couple of directions you might try:

select * from MyTable A where rdate = (select max(rdate) from MyTable B where B.pid = A.pid);

select * from MyTable A where not exists (select pid from MyTable B where B.pid = A.pid and B.rdate > A.rdate)

(i.e. there does not exist a row with a larger rdate value)