Geek asks and answers

SQL Server: how to imitate oracle dense_rank query?

I have an Oracle request

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

which for such data:

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 exactly this to me:

2 10
4 20
6 30

So, for each someId, it searches for the latest updateDate and returns the corresponding one id. (And if there are multiple identifiers for recent dates, they require the latest identifier).

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

+5
source share
4 answers

I do not think your specific query will start SQL Server. But you can achieve the same result:

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

. , , " " . :

  • KEEP-DENSE_RANK Oracle
  • /

. 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

max of m.id (someId, UpdateDate), UpdateDate (someId)

2. - : "MyTable.UpdateDate" , GROUP BY.

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. : ORACLE: http://sqlfiddle.com/#!4/c943c/23 SQL SERVER: http://sqlfiddle.com/#!6/dc7fb/1/0 ( - ) :

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)

wroted #. - , , ... custom aggregate - , varchar. varchar "" aggreate

+3

SQL Server "keep", :

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 UpdateDate. . , .

+1

This will work absolutely. Try it first, then argue. When you have several orders, you can do this (an example made in Oracle):

- this one with keep dense_rank

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

- This one without saving dense_rank

    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
0
source

More articles:


All Articles