返回每个不同的candidate_id具有最新日期的行中的数据

发布于 2024-12-22 23:12:16 字数 291 浏览 2 评论 0原文

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云巢 2024-12-29 23:12:16

您必须对不使用聚合函数的所有内容进行分组：

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分组，那么您必须没有使用消息。在这种情况下，只需将其从选择列表中删除，您的 group by 列表中就不再需要它了。对于您不使用的任何其他字段也是如此。

请记住，使用聚合函数时，必须将每个字段包含在聚合函数或group by 中。否则，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
       )

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
       )

回复收藏 0 原文

栀子花开つ 2024-12-29 23:12:16

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'

这会将 messages 与 Candidate_id 上的自身连接起来，并生成带有选择 m2 中大于 m1 中每个日期的所有日期的行，替换 如果没有更大的，则为 NULL。因此，当 candidate_id 中没有更大的日期时，您会准确地得到 NULL。

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.

回复收藏 0 原文

~没有更多了~