获取与一列MAX值相对应的其他列？ [英] Get other columns that correspond with MAX value of one column?

问题描述

好的，这是我的查询：

 选择
 video_category，
 video_url，
 video_date，
 video_title，
 short_description，
 MAX（video_id）
 FROM videos 
 GROUP BY video_category 
  

当它提取数据时，我得到video_id的正确行，但它为其他行的每个类别拉出第一行。因此，当我获得类别1的video_id的最大结果时，我会得到最大ID，但表格中的第一行是url，日期，标题和说明。

我怎样才能让它拉出与最大ID结果对应的其他列？

编辑：固定。

  SELECT * FROM videos 
 WHERE video_id IN（
 SELECT DISTINCT MAX（video_id）
 FROM videos GROUP BY video_category 
）
 ORDER BY video_category ASC 
  

解决方案

我会尝试这样的：

 选择
 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 
  

即相当快，你自己的解决方案

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

解决方案

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

这篇关于获取与一列MAX值相对应的其他列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！