Help with query to pull unique subset from table

E

earnheam

I hope someone can help with this one. I have a table that I need to
pull certain records from. The table contains an account number that
may be in the table multiple times. There's also a data/time stamp, an
error code, and multiple other fields. What I need to do is pull the
account number only once with the most recent date/time stamp. However,
if the most recent record matches certain error codes, then I need to
pull the most recent record not matching those codes. I still need to
pull records for these certain error codes if they are the only
records.

In the 1st query I created a field for err_rank and if error code
matches certain ones I set err_rank to 8 or 9, else it's set to 1. I
thought this way I would sort the result set by acct, date, err_rank
and first rec should be the one I need. However, I'm not sure now how
to pull only that first record. If I create another query and run it
against the 1st one and group by on acct, date, err_rank, error code it
seems to work, but I then lose all the other fields I need to view. If
I leave all field in 2nd query as group by it still pulls all records.

Does anyone have any ideas?

Thanks for your help.
 
Top