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
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小提琴演示
#2
1
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]
演示
#3
0
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
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小提琴演示
#2
1
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]
演示
#3
0
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