Devin Devin - 1 month ago 8
MySQL Question

Get other columns that correspond with MAX value of one column?

Ok, this is my query:

SELECT
video_category,
video_url,
video_date,
video_title,
short_description,
MAX(video_id)
FROM videos
GROUP BY video_category


When it pulls the data, I get the correct row for the video_id, but it pulls the first row for each category for the others. So when I get the max result for the video_id of category 1, I get the max ID, but the first row in the table for the url, date, title, and description.

How can I have it pull the other columns that correspond with the max ID result?

Edit: Fixed.

SELECT * FROM videos
WHERE video_id IN (
SELECT DISTINCT MAX(video_id)
FROM videos GROUP BY video_category
)
ORDER BY video_category ASC

Answer

I would try something like this:

SELECT
   s.video_id
   ,s.video_category
   ,s.video_url
   ,s.video_date
   ,s.video_title
   ,short_description
FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id = max.id

which is quite faster that your own solution