0

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.

Balázs Búr
  • 5
  • 1
  • 1
    SQL is a language but the details differ per dbms. Please tag the question with the RDBMS you use (mysql, sql server, postgresql, oracle, sqlite, etc...) – ypercubeᵀᴹ Apr 22 '16 at 07:32

1 Answers1

2

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.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • WORKS! This is exactly what I needed! You are awesome! Thank You! – Balázs Búr Apr 22 '16 at 07:46
  • 1
    Welcome. Please don't forget to add the DBMS in your questions. It's also good if you add the tables' design (CREATE TABLE) and indexes, so people can give better answers. If this were for PostgreSQL or SQL Server, I would have added more (and different) ways to accomplish the result. – ypercubeᵀᴹ Apr 22 '16 at 07:49