返回具有每个不同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.

我试图从具有每个不同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

2 个解决方案

#1

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.

如果您的邮件每行不同,并且您希望按candidate_id分组,那么您一定不能使用邮件。在这种情况下,只需将其从选择列表中删除,您就不需要在列表中分组。对于您未使用的任何其他字段也是如此。

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.

请记住,使用聚合函数时,必须在聚合函数或group by中包含每个字段。否则,SQL将不知道从哪行中提取返回的行的数据。

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
       )

#2

SELECT m1.*
FROM messages as m1
  LEFT OUTER JOIN messages AS m2
    ON    (m1.candidate_id = m2.candidate_id
      AND (m1.created_unix < m2.created_unix)
WHERE m2.created_unix is NULL
AND employer_id='$employerid' AND last='company'

This joins messages to itself on candidate_id and makes rows with picks all dates in m2 that are greater than each date in m1, substituting NULL if none are greater. So you get NULL precisely when there is no greater date within that candidate_id.

这会在candidate_id上将消息连接到自身,并使得选择所有日期的行以m2为单位,大于m1中的每个日期,如果没有更大则替换为NULL。因此,当该candidate_id中没有更大的日期时,您会精确地获得NULL。

#1