How to Retrieve Corresponding Column Values for the Maximum Value in SQL?

Getting Corresponding Column Values for Maximum Value

In SQL, you may encounter situations where you want to retrieve column values that correspond with the maximum value of another column, while ensuring that the retrieved data is accurate.

Problem:

When querying a table to retrieve specific columns and group the results based on a column, such as video category, you may observe that for the maximum value of the designated column (e.g., video_id), it pulls the correct value but returns the first row in the table for all other columns (e.g., video_url, video_date, video_title, and short_description).

Solution:

To retrieve the columns corresponding to the maximum value appropriately, consider the following approach:

SELECT
    s.video_id
    ,s.video_category
    ,s.video_url
    ,s.video_date
    ,s.video_title
    ,s.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

This query leverages a JOIN operation to match the video ID (video_id) from the subquery that calculates the maximum video ID (id) for each video category with the video ID from the videos table. By using the JOIN condition s.video_id = max.id, it ensures that only the row with the maximum video ID for each category is returned.

This approach not only provides the correct corresponding values for the maximum video ID but also performs significantly faster than the proposed solution in the original post.

The above is the detailed content of How to Retrieve Corresponding Column Values for the Maximum Value in SQL?. For more information, please follow other related articles on the PHP Chinese website!