Search last record of a different group

Navigation
1

I'm developing a small vaccine control system and I'm having a hard time performing a select.

I have a record of dogs, another of types of vaccines and a third of vaccine per dog, where I group the dog's id, type id of the vaccine (v10, antirabica, fleas, etc.), application date and the date of the next application.

I'm trying to look at this vaccine record per dog only the latest application per dog grouping and type, for example: 

Cachorro  Vacina  DtAplicação  DtProxima
1         1       10/03/16     10/03/17
1         1       10/03/15     10/03/16
1         2       10/03/16     10/03/17
2         1       10/03/16     10/03/17
2         2       10/03/16     10/03/17

In this example I need to display all records, except the second (because it is an already reinforced application of the same type for the same dog).

Here's an example of what I'm trying to do to get the vaccines to be applied in the current week but not filtering properly: 

SELECT c.nome, cv.vacinaoid, max(cv.proxima) FROM bicho_cachorro c 
INNER JOIN bicho_cachorro_vacina cv ON c.id = cv.cachorrooid 
WHERE WEEKOFYEAR( cv.proxima ) = WEEKOFYEAR( NOW() ) 
group by 1, 2 order by 3

How can I do this search? I tried to make a distinct of c.nome and cv.vacionaoid , but could not make the two fields together.

    
asked by anonymous 10.03.2017 / 17:12

1 answer

0

I made a workaround, however, I can only test in SQL Server. I used the concept of subqueries, where the internal query selects the last date of each group and the external query joins the other fields. I used the same field names as you used in the example. 

select vacinas.* from vacinas inner join
(
    select cachorro, vacina, MAX(DtAplicação) as DtAplicação from vacinas group by cachorro, vacina
)T
ON vacinas.cachorro = T.cachorro AND vacinas.vacina=T.vacina AND vacinas.DtAplicação=T.DtAplicação
ORDER BY cachorro, vacina

To select the vaccines of the week is easier 

select * from vacinas where Datepart(WK, DtAplicação) = Datepart(WK, GETDATE())

You will have to change the function that gets the week, in my case I used the SQL Server function. The MySql function is WEEK ( documentation here ) p>

I hope it helps.

    
10.03.2017 / 18:54
ngDisable does not validate two functions within the onchange