Select all the last dates from all list of id

I’m trying to select all the videos that were last inserted, without repeating any ID_video_type (id_video_tipo column).

Here’s an example of the data int the database:

I’m trying to run the following query

SELECT *
FROM `video` 
GROUP BY id_video_tipo
ORDER BY data DESC

but when I do that I get the following result

As you can see, I managed to get the videos to show up without the id_video_tipo repeating, but I can’t get the video with the latest date to show up. For example, for id_video_tipo = 6 it should have shown video with id = 3. Instead, it shows video with id = 2. How can I fix it?

Answer

You can do it with a correlated subquery in the WHERE clause which returns the last date for each id_video_tipo:

SELECT v.*
FROM `video` v
WHERE v.data = (SELECT MAX(data) FROM `video` WHERE id_video_tipo = v.id_video_tipo)

or with NOT EXISTS:

SELECT v.*
FROM `video` v
WHERE NOT EXISTS (
  SELECT 1 FROM `video`
  WHERE id_video_tipo = v.id_video_tipo AND data > v.data
)