我不认为您的特殊查询会运行 SQL 服务器。 但是您可以实现同样的结果 :

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

不幸的是,在使用“排位窗口功能”的移民变得非常复杂的情况下,存在着几种情况。

1. many KEEP-DENSE_RANK constructions in the select part of Oracle query based on different orders
2. grouping by grouping sets/rollups

Therefore I will add to the answer additional information. Original data SQLFIDDLE: http://sqlfiddle.com/#!6/e5c6d/6

<强 > 1. 读取符元函数 :

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

我们在其中选择组中最大 m. id 的 m. id 值( 约Id, 更新日期), 更新日期最大, 组中最大( 约Id) 。

2. 直向前进路 doesn t working ,因为错误: Column My Table。 选中列表中更新日期无效, 因为它既不包含在总计函数中, 也不包含在分组中 。

SELECT FIRST_VALUE(id) OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) first_in_orderedset , someId
FROM MyTable
GROUP BY someId 

<强 > 3. 直直直前方的杂草是无效的

SELECT someId, MIN(first_in_orderedset)
FROM
 ( SELECT FIRST_VALUE(id) OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) first_in_orderedset , someId
   FROM MyTable ) t
GROUP BY someId;

<强>4. 交叉号适用:

SELECT grouped.someId, orderedSet.FirstUpdateDate, maxInSet.first_in_orderedset FROM
(
    SELECT mt.someId 
    FROM MyTable mt
    GROUP BY mt.someId
) grouped CROSS APPLY 
( 
   SELECT top 1 mt2.UpdateDate as FirstUpdateDate  
   FROM MyTable mt2 
   WHERE mt2.someId=grouped.someId  
   ORDER BY UpdateDate desc
) orderedSet  CROSS APPLY
( 
   SELECT max(mt3.id) as first_in_orderedset 
   FROM MyTable mt3 
   WHERE mt3.someId=grouped.someId  and mt3.UpdateDate=orderedSet.FirstUpdateDate  
) maxInSet;

5. Now lets get the more complex table and more complex query: ORACLE : http://sqlfiddle.com/#!4/c943c/23 SQL SERVER: http://sqlfiddle.com/#!6/dc7fb/1/0 (data is pregenerated and it is the same in both sandboxes - it is easy to compare results) Table:

CREATE TABLE AlarmReports (
  id int PRIMARY KEY,
  clientId int, businessAreaId int , projectId int, taskId int,  
    process1Spent int, process1Lag int, process1AlarmRate varchar2(1) null,
    process2Spent int, process2Lag int, process2AlarmRate varchar2(1) null,
    process3Spent int, process3Lag int, process3AlarmRate varchar2(1) null
)

Oracle 查询 :

SELECT clientId, businessAreaId, projectId, 
  sum(process1Spent),
  sum(process2Spent),
  sum(process3Spent),
  MIN(process1AlarmRate) KEEP (DENSE_RANK FIRST ORDER BY process1Lag DESC),
  MIN(process2AlarmRate) KEEP (DENSE_RANK FIRST ORDER BY process2Lag DESC),
  MIN(process3AlarmRate) KEEP (DENSE_RANK FIRST ORDER BY process3Lag DESC)
FROM AlarmReports 
GROUP BY GROUPING SETS ((),(clientId),(clientId, projectId),(businessAreaId),(clientId,businessAreaId))

SQL 查询 :

(to be continued)

实际上,我计划把我的定制总合 写在c##。 如果有人感兴趣, 请联系我... 定制总合是解决这类问题的最好办法... 但对于每个纵曲长度来说,它不是没有生命力的。 您必须创建“ 专门化的” 括号函数 。

That will work absolutely. Try first, then argue. When you have multiple order by you can do this(example made on Oracle):

- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

WITH a AS (SELECT 1 s1, 4 s2,  a  c,  10 g FROM dual UNION all
           SELECT 2 s1, 2 s2,  b  c,  10 g FROM dual UNION ALL
           SELECT 3 s1, 1 s2,  c  c,  20 g FROM dual UNION ALL
           SELECT 4 s1, 3 s2,  d  c,  20 g FROM dual)
SELECT g,
       MAX(c) KEEP (DENSE_RANK FIRST ORDER BY s1) s1,
       MAX(c) KEEP (DENSE_RANK FIRST ORDER BY s2) s2
  FROM a
 GROUP BY g

- 这个没有保持密度的

    WITH a AS (SELECT 1 s1, 4 s2,  a  c,  10 g FROM dual UNION all
               SELECT 2 s1, 2 s2,  b  c,  10 g FROM dual UNION ALL
               SELECT 3 s1, 1 s2,  c  c,  20 g FROM dual UNION ALL
               SELECT 4 s1, 3 s2,  d  c,  20 g FROM dual)
SELECT g,
       MAX(DECODE(s1, 1, c)) s1,
       MAX(DECODE(s2, 1, c)) s2
  FROM (SELECT g,c,
               ROW_NUMBER() OVER (PARTITION BY g ORDER BY s1) s1,
               ROW_NUMBER() OVER (PARTITION BY g ORDER BY s2) s2 
          FROM a) b
 GROUP BY g

SQL 服务器不支持“ 保存” 构造, 所以您需要使用子查询 :

select m.*
from (select *, row_number() over (partition by m.someid ORDER BY m.UpdateDate desc) as seqnum
      from MyTable m
     ) m
where seqnum = 1

这里找到每个 m. id 的第一行, 上面有最新的更新日期 。 然后在外部查询中选择该行 。 请注意, 您不需要使用此方法组合 。

如果有人在Postgres寻找 Oracle KOEP DENSE_RANK 模拟:

CREATE TABLE myt (
  "id" INTEGER,
  "update_date" timestamp,
  "some_id" INTEGER
);

INSERT INTO myt
  ("id", "update_date", "some_id")
VALUES
  ( 1 ,  2012-01-20 ,  10 ),
  ( 2 ,  2012-01-20 ,  10 ),
  ( 3 ,  2012-01-01 ,  10 ),
  ( 4 ,  2012-10-02 ,  20 ),
  ( 5 ,  2012-01-02 ,  20 ),
  ( 6 ,  2012-01-04 ,  30 );                         


select
    some_id, 
    (array_agg(id order by update_date desc, id desc))[1]
from myt
group by some_id
order by some_id