Distinct Select Top Values

[xentaur]

Hi everyone

BACKGROUND:
I developed and administer a case noting system for our organisation. I am trying to institute a 'history' function for users - to save time conducting a search they can just click a button to view the 10 most recent records updated.

The three relevant fields in TblA to source the information are:
pkey - primary key, incremental, no duplicates.
clid - client ID
uid - the logged in user=environ("username")

ISSUE:
I'm fighting with DISTINCT in the query sytax. The most recent entries for the user will be the highest pkey value, but the user may have made more than one entry against a given clid.

What I want is:

SELECT TOP 10 most recently accessed clid's
FROM TblA
WHERE uid = environ("username")
ORDER BY pkey DESC;

but in a situation where a users history contains more than one entry for a clid, eg:

pkey     clid     uid
1000     111      me
0999     111      me
0970     111      me
0965     142      me
0940     111      me
0935     130      me

... then a DISTINCT or DISTINCT ROW is going to pull all 4 instances. If these were the only entries for user 'me' then I want the query results to return 111, 142 and 130 in that order.

My brain hurts and I'm hoping someone can offer a solution.

Thanks all,

Cheers

 

[PHV]

Perhaps this ?
SELECT TOP 10 Max(pkey), clid
FROM TblA
WHERE uid = environ("username")
GROUP Y clid
ORDER BY 1 DESC;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886