SQL Server:如何模仿oracle保持density_rank查询? [英] SQL Server: how to imitate oracle keep dense_rank query?

问题描述

我有一个Oracle查询

I have an Oracle query

select max(m.id),
       m.someId keep (DENSE_RANK FIRST ORDER BY m.UpdateDate desc) 
from MyTable m 
groupBy m.someId

对于这样的数据:

id UpdateDate someId
1  20-01-2012 10
2  20-01-2012 10
3  01-01-2012 10
4  10-02-2012 20
5  01-02-2012 20
6  01-04-2012 30

将完全返回给我:

2 10
4 20
6 30

因此，它为每个someId搜索最新的updateDate并返回适当的id. (并且，如果有多个最新日期的ID，它将采用最新ID).

So, for every someId it searches for latest updateDate and does return the appropriate id. (And if there are a several ids for the latest dates it takes latest id).

但是对于SQL Server，此查询将以相同的方式工作吗?我的意思是这种构造keep (dense_rank first order by ..)?

But for SQL server will this query work the same way? I mean this construction keep (dense_rank first order by ..)?

推荐答案

我不认为您的特定查询将运行SQL Server.但是您可以通过执行以下操作获得相同的结果:

I don't think that your particular query will run SQL Server. But you can achieve the same result doing this:

SELECT id, SomeId
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) Corr
        FROM MyTable) A
WHERE Corr = 1

这篇关于SQL Server:如何模仿oracle保持density_rank查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！