Query the last records by date, where condition is true A made this query, which is works perfectly now:

SELECT t.DocID, t.RevID, t.EffDate
FROM tblRev AS t
INNER JOIN (
    SELECT DocID, max(EffDate) AS MAXEffDate
    FROM tblRev GROUP BY DocID
) AS tm ON (t.EffDate = tm.MAxEffDate) AND (t.DocID = tm.DocID);


Basically in my document database, All documents have revisions (1.0, 1.1, 1.2 etc.). I could query the last revisions by date, but there is an tblRev.OK column (true/false) in a table.

I need to insert a WHERE statement to query the last Revision where tblRev.OK=true.

For example(table):

 DocID - RevID - RevDate - OK
   1   -  1.0  -  jan 1  - true
   1   -  1.1  -  feb 2  - true
   1   -  1.2     march3 - false


So I wanted this line: 1   -  1.1  -  feb 2  - true

I can do it if i made query to list al Revs where=true, but I should do it in one query if its possible.
There are various ways to do this kind of queries (greatest-n-per-group) which also differ per DBMS (in syntax and performance) but the standard SQL you have will work in almost all products.

Without changing the structure of your query, you just need to add the condition in both the external and the internal subquery:

SELECT t.DocID, t.RevID, t.EffDate
FROM tblRev AS t
INNER JOIN (
    SELECT DocID, max(EffDate) AS MAXEffDate
    FROM tblRev
    WHERE OK = TRUE 
    GROUP BY DocID
) AS tm ON (t.EffDate = tm.MAxEffDate) AND (t.DocID = tm.DocID)
WHERE t.OK = TRUE ;


If there is a UNIQUE constraint on (DocID, RevDate), the external check (WHERE t.OK = TRUE) is not needed.

Minor (improvement or obfuscation?): the WHERE OK = TRUE can be shortened to WHERE = OK, since OK holds boolean values.