You could try this:

SELECT *
FROM _table_ t1
INNER JOIN (
SELECT MAX(RecordCount) as LastRecordCount FROM _table_
GROUP BY CardNumber, CompanyID ) as t2 ON t1.RecordCount =
t2.LastRecordCount


Hope this helps.
Igor Raytsin


"Ted" <no@thanks.invalid> wrote in message news:200311166112.081904@dev...
Here's what I'm trying to do:

I have a table, which I don't have administration over, with 4 interesting
fields:
RecordCount - an auto incrementing primary key
CardNumber - integer
CompanyID - integer
AccessPriv - an string that varies constantly

There could be identical CardNumber's, but they must have different
CompanyId's. A data set might look like this:

RecordCount | CardNumber | CompanyID | AccessPriv
1 | 1 | 82 | all
2 | 2 | 82 | level 1
3 | 2 | 84 | all
4 | 1 | 82 | none

The table is transactional, so old records will not be flushed even though
new records contain the most current data (records 1 and 4 in this case).
I'm wondering if there's a way to do a SQL SELECT query that, as it goes
from the beginning to the end of the table, overwrites previous records when
later CardNumber's and CompanyID's match the previous records. So in this
case, the query would only return rows 2-4 because record 4's CardNumber and
CompanyID match record 1's.

I know this is possible with application logic, but is it possible with an
SQL query?

Thanks.