Getting one result of each id, but only the one with the newest date

Question

I have a system that stores documents in a database, each user can create multiple documents and each document is saved in different versions. Each user has a unique token. Each document has a unique id. Each document is being stored many times but at different times.

Example of my table:

|AI  |token |docid   time|
|1   |id1   |doc1    1000|
|2   |id2   |doc2    1001|
|3   |id1   |doc1    909 |
|4   |id2   |doc1    1020|
|5   |id1   |doc3    801 |

What I need is a query that gives me only the newest of each document for a particular user.

So in this example I would like for user with token id1:

1   id1   doc1    1000
5   id1   doc3    801

How would a query that achieve this look like?

Answer 1

With this query:

select token, docid, max(time) time
from tablename
group by token, docid

you get the newest time for each token and docid.
If you don't need the AI column then you're done.
If you also need the AI column you must join this query to the table:

select t.* 
from tablename t inner join (
  select token, docid, max(time) time
  from tablename
  group by token, docid
) g on g.token = t.token and g.docid = t.docid and g.time = t.time

If you want results only for token = 'id1', you can add a condition:

where t.token = 'id1'

or you can also use NOT EXISTS:

select t.* 
from tablename t
where 
  t.token = 'id1'
  and not exists (
    select 1 from tablename
    where token = t.token and docid = t.docid and time > t.time
  );

See the demo.
Results:

| ai  | token | docid | time |
| --- | ----- | ----- | ---- |
| 1   | id1   | doc1  | 1000 |
| 5   | id1   | doc3  | 801  |