在特定列中选择整个表，但要选择唯一的值

Please help me with an issue that I have come across during work. I'm working with SQL Server, and I'm aware that using cursors I could achieve this, but I'm pretty sure that there is a way of doing it using simple query in SQL, but my brain bulb doesn't want to turn on. Let me explain my issue with an example.

请帮我解决工作中遇到的问题。我正在使用SQL Server，我知道使用游标可以实现这一点，但是我很确定有一种方法可以使用SQL中的简单查询来实现这一点，但是我的大脑不想打开它。让我用一个例子来解释我的问题。

I have got a table like this:

我有一张这样的桌子:

postedby    |   date        |   comment |
1           |   01.01.2012  |   sth sth |
2           |   01.01.2012  |   sth sth |
3           |   01.01.2012  |   sth sth |
2           |   01.01.2012  |   sth sth |
3           |   02.01.2012  |   sth sth |
2           |   03.01.2012  |   sth sth |
2           |   05.01.2012  |   sth sth |

What I want to accomplish is get all the posts but one for every user (postedby column), the date must be the latest and of course show the comment.

我想要完成的是获得所有的帖子，但每个用户(postedby列)只有一个，日期必须是最近的，当然还要显示评论。

I have tried doing:

我已经尝试做:

Select distinct postedby, date, comment

but didn't work, as I understand distinct works for every column, so if in 2 rows postedby is the same but comment is different it will treat it as distincts

但是没有用，因为我理解每一列都有不同的作品，所以如果在两行postedby是相同的但是注释是不同的它会把它当作区别对待

I have tried doing:

我已经尝试做:

Select postedby,date,comment group by postedby (don't bother about the from clause) giving me the error or aggregation, so I tried select postedby,min(date) group by postedby - of course works, but I can't get the comment.

通过postedby选择postedby、date、comment group(不要麻烦from子句)来给我提供错误或聚合，因此我尝试通过postedby选择postedby、min(date) group—当然可以，但是我无法得到注释。

Should I use in some way aggregated queries? Or what am I missing?

我应该以某种方式使用聚合查询吗?或者我错过了什么?

2 个解决方案

#1

Looks like today is the RowNumber function day!! If all you needed is the latest date and comment for each post:

看来今天是行号功能日!!如果你所需要的只是最新的日期和每个帖子的评论:

SELECT postedBy, date, comment
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY postedby, ORDER BY date DESC) AS RowNumber, 
             postedBy, date, comment
      FROM MyTable) t 
WHERE RowNumber = 1

#2

You tried min(date). It will return min date of unique value. Have you tried max(date)? It will be better for that. The max date always return the latest date.

你试过min(日期)。它将返回唯一值的最小日期。你有试过马克斯(日期)?这样会更好。最大日期总是返回最近的日期。

#1