从具有每个不同candidate_id的最近日期的行中返回数据 [英] Return the data from the rows with the most recent date of each distinct candidate_id

问题描述

我试图从每个不同的candidate_id的最近日期的行中返回数据。它正确地返回最近的日期（created_unix列），而不是相应行中的其余数据。

  SELECT candidate_id，message，max（created_unix），jobpost_id，staffuserid 
 FROM messages 
 WHERE employer_id ='$ employerid'AND last ='company'
 GROUP BY candidate_id 
  
解决方案
您必须 group by  everything不使用聚合函数：
 
 $ p $  SELECT candidate_id，message，max（created_unix），jobpost_id，staffuserid 
 FROM messages 
 WHERE employer_id ='$ employerid'AND last ='company'
 GROUP BY candidate_id，message，jobpost_id，staffuserid 
  

如果您的消息每行不同，并且您希望通过candidate_id 组<，那么你不能使用消息。在这种情况下，只需将它从您的选择列表中删除，并且您不需要它在组中按列表排序。请注意，使用聚合函数时，必须包含聚合函数或按分组。否则，SQL将不知道从哪一行获取返回的行的数据。

更新：

在看到您要找的内容后，这可以实现：

  SELECT candidate_id， message，max（created_unix），jobpost_id，staffuserid 
 FROM messages 
 WHERE employer_id ='$ employerid'AND last ='company'AND 
 created_unix =（
 SELECT max（subm。 created_unix）
 FROM messages subm 
 WHERE subm.candidate_id = messages.candidate_id 
）
  

I am attempting to return the data from the rows with the most recent date of each distinct candidate_id. It is correctly returning the most recent date (the created_unix column), but not the rest of the data from the corresponding row.

SELECT candidate_id, message, max(created_unix), jobpost_id, staffuserid 
    FROM messages 
       WHERE employer_id='$employerid' AND last='company' 
          GROUP BY candidate_id

解决方案

You must group by everything not using an aggregate function:

SELECT candidate_id, message, max(created_unix), jobpost_id, staffuserid 
    FROM messages 
       WHERE employer_id='$employerid' AND last='company' 
          GROUP BY candidate_id, message, jobpost_id, staffuserid 

If your message is different per row and you want to group by candidate_id, then you must not be using message. In that case, simply remove it from your select list and you won't need it in your group by list. The same goes for any other field you aren't using.

Remember, when using aggregate functions, you must contain each field in either an aggregate function or the group by. Otherwise, SQL won't know from which row to pull the data for the row returned.

Update:

After seeing what you're looking for, this will do the trick:

SELECT candidate_id, message, max(created_unix), jobpost_id, staffuserid 
    FROM messages 
       WHERE employer_id='$employerid' AND last='company' AND
       created_unix = (
           SELECT max(subm.created_unix)
           FROM messages subm
           WHERE subm.candidate_id = messages.candidate_id
       )

这篇关于从具有每个不同candidate_id的最近日期的行中返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！