Field referring to MAX

Navigation
3

I have the following table / fields:

Table: PLANS

  • ID (PK)
  • VEICULO (REPEAT)
  • DATAINCLUSAO
  • REVISAO (UNIQUE)

I need to get the number of REVISAO of each VEICULO of last DATAINCLUSAO .

So I can get VEICULO and last DATAINCLUSAO , but if I put REVISAO , it will ask to add in GROUP BY , and I can not, since REVISAO is unique, so it would bring me everything.

If I put MAX(REVISAO) can bring me incorrect value. 

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

In short, what I need is to get the value of REVISAO , referring to the last DATAINCLUSAO of each VEICULO .

    
asked by anonymous 04.10.2018 / 16:10

3 answers

4

In addition to the answer already given, it is also possible with an equivalent subquery 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)

SQL Fiddle Example

    
04.10.2018 / 16:32
2

Another way to do it: 

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

It's a safer query 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.

    
04.10.2018 / 16:58
1

You can control this in a subquery, where you sort by datainclusao , loading only the most recent: 

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)
    
04.10.2018 / 16:19
Increase numbers to ID name JSON PARSE returning 'object object'