Field referring to the MAX

Question

I have the following table/fields:

Table: PLANS

• ID (PK)
• VEICULO (IS REPEATED)
• DATAINCLUSAO
• REVISAO (UNIQUE)

I need to bring the number of REVISAO of each VEICULO of the latter DATAINCLUSAO.

So I can bring the VEICULO and the last DATAINCLUSAO, but I put myself REVISAO, will ask to add to GROUP BY, and I can’t, because REVISAO is unique, so it would bring me everything.

Place MAX(REVISAO) may bring me incorrect value.

SELECT VEICULO, MAX(DATAINCLUSAO)
FROM PLANOS
GROUP BY VEICULO

---

In short: what I need is to bring the value of REVISAO, concerning the last DATAINCLUSAO of each VEICULO.

Answer 1

In addition to the answer already given, it is also possible with an equivalent sub-consumption using max():

SELECT P1.VEICULO, P1.DATAINCLUSAO, P1.REVISAO
FROM PLANOS P1
WHERE P1.DATAINCLUSAO =
       (SELECT MAX(P2.DATAINCLUSAO)
        FROM PLANOS P2
        WHERE P2.VEICULO = P1.VEICULO)

Example in SQL Fiddle

Answer 2

Another way of doing:

SELECT      P1.VEICULO
        ,   P1.REVISAO
        ,   P2.DATAINCLUSAO
FROM        PLANOS  P1
INNER JOIN  (
                SELECT      MAX(DATAINCLUSAO) AS DATAINCLUSAO
                        ,   VEICULO
                FROM        PLANOS
                GROUP BY    VEICULO
            )       P2  ON  P2.VEICULO      = P1.VEICULO
                        AND P2.DATAINCLUSAO = P1.DATAINCLUSAO

Is a query safer because it has the vehicle filter, beyond the date.

If you happen to have more than one vehicle on the same date, this query does not return more than one result per vehicle.

Answer 3

You can control that in a sub-colony, where it’s ordered by datainclusao, loading only the latest:

SELECT DISTINCT VEICULO, DATAINCLUSAO, REVISAO 
FROM PLANOS P1
WHERE P1.ID = (SELECT TOP 1 P2.ID 
               FROM PLANOS 
               WHERE P1.VEICULO = P2.VEICULO 
               ORDER BY DATAINCLUSAO DESC)