SQL how to select only single row using joining?

I have the tables:

tblClients:

+----+-------+
| ID | sName |
+----+-------+
| 22 | User1 |
| 23 | User2 |
+----+-------+

tblKeys:

+----+-------+
| ID | sValue|
+----+-------+
| 1  | Key1  |
| 2  | Key2  |
+----+-------+

tblEntries:

+----+---------------------+-----------+------------+
| ID |       datDate       | lClientID | lKeyID     |
+----+---------------------+-----------+------------+
|  1 | 22.10.2019 22:16:34 |        22 |            |
|  2 | 22.10.2019 22:16:34 |        22 |          1 |
|  3 | 22.10.2019 22:16:30 |        23 |            |
|  4 | 22.10.2019 22:16:38 |        23 |          1 |
+----+---------------------+-----------+------------+

lClientID and lKeyID (optional) refer to above tables.

I need a SQL query which gives me the list of the users from the tblEntries only with maximum datDate field. Now i used the follwing SQL query:

SELECT DISTINCT tblEntries.lClientId, tblEntries.datDate, tblKeys.sValue
FROM (tblEntries INNER JOIN (SELECT lClientId, MAX(datDate) AS datLastAccess FROM tblEntries GROUP BY lClientId)  AS z1 ON (tblEntries.datDate = z1.datLastAccess) AND (tblEntries.lClientId = z1.lClientId)) LEFT JOIN tblKeys ON tblEntries.lKeyId = tblKeys.ID;

The problem i encountered is the datLastAccess can contains the equal values so i get the duplicates with the two different keys. I need only the single entry per lClientID. If there are two items with the same values of datDate and lClientID but with the different lKeyID i need only the last one.

Now i get:

+-----------+---------------------+--------+
| lClientId |       datDate       | sValue |
+-----------+---------------------+--------+
|        22 | 22.10.2019 22:16:34 |        |
|        22 | 22.10.2019 22:16:34 | Key1   |
|        23 | 22.10.2019 22:16:38 |        |
+-----------+---------------------+--------+

I don’t need the first row (with empty sValue)

Thanks in advance.

Answer

In MS Access, I would recommend:

select l.lclientId, l.datdate, k.svalue
from tblEntries e inner join
     tblKeys k
     on e.lkeyId = k.keyId
where l.id = (select top (1) l2.id
              from tblEntries l2
              where l2.lclientId = l.lclientId
              order by l2.datdate desc, l2.id desc
             );

Notes:

• You don’t need tblClients for this query.
• The where clause is getting exactly one matching row for each entry per client, because it is comparing on the id column.
• There is no requirement that ids increase with the date — although if that is the case, the query can be further simplified.

In addition, the subquery can take advantage of an index on tblEnties(lclientId, datdate desc, id desc).