SQL Server: how to imitate oracle keep dense_rank query?

Question

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

which for data like this:

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

will return me exactly this:

2 10
4 20
6 30

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).

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

Answer 1

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

Answer 2

I return and return to this question and the answer. Unfortunately there are several situations when migration using the "window function for ranking" become very complex. Those situations are:

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. Reading oracle function:

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

there we select max of m.id in the group (someId, UpdateDate) where UpdateDate is biggest it the group (someId)

2. straight forward way doesn't work because of error: Column 'MyTable.UpdateDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

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

3. improoved 'straight forward' is noneffective

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. cross apply:

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 query:

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 query:

(to be continued)

actually there I have planned to put my custom aggregate wroted with c#. if somebody are interested, please contact me... custom aggregate is the best solution of such problems but it is not unviersal in terms of varchar lengths. for each varchar length you would be obligated to create "specialised" aggreate function