选择最大日期的唯一位置 [英] Select distinct where date is max

问题描述

问这个问题真的很愚蠢，但是我无法在SQL Server Compact(CE)中进行此选择

This feels really stupid to ask, but i can't do this selection in SQL Server Compact (CE)

如果我有两个这样的表:

If i have two tables like this:

Statuses                      Users
id | status   | thedate        id | name
-------------------------      -----------------------
0  | Single   | 2014-01-01     0  | Lisa
0  | Engaged  | 2014-01-02     1  | John
1  | Single   | 2014-01-03
0  | Divorced | 2014-01-04

我现在如何为状态中的每个人选择最新状态? 结果应该是:

How can i now select the latest status for each person in statuses? the result should be:

Id | Name | Date       | Status
--------------------------------
0  | Lisa | 2014-01-04 | Divorced
1  | John | 2014-01-03 | Single

，即，选择日期最高的唯一ID:S，并加入名称.作为奖励，对列表进行排序，以使最新记录排在最前.

that is, select distinct id:s where the date is the highest, and join the name. As bonus, sort the list so the latest record is on top.

推荐答案

在SQL Server CE中，可以使用join:

In SQL Server CE, you can do this using a join:

select u.id, u.name, s.thedate, s.status
from users u join
     statuses s
     on u.id = s.id join
     (select id, max(thedate) as mtd
      from statuses
      group by id
     ) as maxs
     on s.id = maxs.id and s.thedate = maxs.mtd;

子查询计算最大日期并将其用作statuses表的过滤器.

The subquery calculates the maximum date and uses that as a filter for the statuses table.

这篇关于选择最大日期的唯一位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！