Search last record of a distinct group

Asked

Viewed 703 times

1

I’m developing a small vaccine control system and I’m having a hard time executing a select.

I have a record of dogs, other types of vaccines and a third of dog vaccine, where I group the dog’s id, the vaccine type id (V10, antirabica, antipulgas, etc), the date of application and the date of next application.

I’m trying to search in this register of dog vaccine only the latest application by grouping of dog 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 above, I need all records to be displayed, except the second one (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 vaccines to be applied in the current week, but it’s 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 I couldn’t make the two camps together.

mysql select-sql distinct

  • What is the rule for records that should not be shown?

  • For example: the dog 1 took vaccine 1 last year and in this, then will have two entries with the first two fields 1, but with different dates of application and reinforcement. I need to display only the latest application from each group. A record of the dog 1 with vaccine 1, another of the dog 1 with vaccine 2, another of the dog 2 with vaccine 1, another of the dog 2 with vaccine 2 and so on, always keeping the last application displayed.

1 answer

0


I made a solution however, I only have to 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 does Join with the other fields. I used the same field names 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 vaccinations of the week is easier

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

you will have to change the function you get the week, in my case I used the SQL Server function. Mysql function is WEEK (documentation here)

I hope it helps.

Browser other questions tagged mysql select-sql distinct

You are not signed in. Login or sign up in order to post.