SQL组通过并分配最高的值

I am struggling with the SQL (mssql) to manipulate my data as i need it. I have a table like this;

我正在使用SQL (mssql)来处理我需要的数据。我有一张这样的桌子;

SOMEID, SOMEFIELD, DATE
5        True    01-01-2010
5        True    01-01-2011
5        False   05-05-2012
7        True    05-05-2011
7        False   06-07-2015

What I am trying to achieve is to add another column which assigns the value 1 if they are the most recent for that ID, and 0 if not. So in the above data example the new column values from top to bottom would be 0, 0, 1, 0, 1.

我要做的是添加另一列，如果它们是最近的ID，则赋值为1，如果不是，则为0。在上面的数据示例中，从上到下的新列值将是0,0,1,0,1,0,1。

I know I need to group by date but am having trouble assigning the values.

我知道我需要按日期分组，但在分配值时遇到了麻烦。

Thanks for any pointers!

感谢任何指针!

3 个解决方案

#1

You can use row_number() in SQL Server like this:

您可以在这样的SQL服务器中使用row_number():

select *
   , case when (row_number() over (partition by SOMEID order by [Date] desc)) = 1 then 1 else 0 end seq
from 
    yourTable
order by 
    SOMEID, [Date];

SQL Fiddle Demo

SQL小提琴演示

#2

You can use a self join to get the highest row per group then in update query use a case statement to assign value to new column

可以使用自连接获取每个组的最高行，然后在update query中使用case语句为新列赋值

update a
set a.[somecol] = case when b.[SOMEID] is null then 1 else 0 end
from demo a
left join demo b on a.[SOMEID] = b.[SOMEID]
and a.[DATE] < b.[DATE]

DEMO

演示

#3

try this

试试这个

SELECT SOMEID, SOMEFIELD, DATE 
, CASE WHEN (SELECT MAX(SubTab.Date) 
             FROM myTable SubTab 
             WHERE SubTab.SOMEID = myTable.SOMEID
            ) = myTable.DATE 
        THEN 1 ELSE 0 END
FROM myTable

#1