CODEWITHSUNDEEP
sqlsql-serveroracle-database
canada canada
canada canada

Reputation: 181

SQL Server:keep dense_rank last order by

I have the following query with Oracle 

select 
        max(DW_EXTRACT_DATE)                as DW_EXTRACT_DATE, 
        DW_LOGICAL_DATE     as SELECTION_DATE, 
        max(DW_LOGICAL_DATE)                as DW_LOGICAL_DATE, 
        max(RUNTIME_AUDIT_ID)               as RUNTIME_AUDIT_ID, 
        max(REC_SRC_SYS)                as REC_SRC_SYS, 
        POSITION_ID, 
        EMPLOYEE_ID,  
            PRIMARY_POS,
            INV_STR_DATE,
            max(POS_STOP_DATE) keep (dense_rank last order by INV_STR_DATE)     as POSITION_STOP_DATE,
            max(CHANGE_REASON) keep (dense_rank last order by INV_STR_DATE)     as CHANGE_REASON_CD,
            max(ACTUAL_FTE_PERC) keep (dense_rank last order by INV_STR_DATE)   as ACTUAL_FTE_PERC
      from 
        EMPLOYEE_POSITION 
      where
        POSITION_ID      != 'TERMINATED'
      group by 
        DW_LOGICAL_DATE, 
        POSITION_ID, 
        EMPLOYEE_ID, 
        PRIMARY_POS, 
        INV_STR_DATE

How to do the same with sql server ? I am a new with oracle .

I tried the following query .

SELECT  DW_EXTRACT_DATE,DW_LOGICAL_DATE,RUNTIME_AUDIT_ID,REC_SRC_SYS,REC_SRC_SYS,POSITION_ID,

        EMPLOYEE_ID,  PRIMARY_POS,INV_STR_DATE,POS_STOP_DATE,
            CHANGE_REASON,
            ACTUAL_FTE_PERC
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY POS_STOP_DATE ORDER BY INV_STR_DATE DESC) Corr
        FROM EMPLOYEE_POSITION
         where POSITION_ID      != 'TERMINATED') A WHERE Corr = 1

Upvotes: 0

Views: 4504

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

In SQL Server 2012+, you can use FIRST_VALUE():

So (to keep things simple):

SELECT max(POS_STOP_DATE) keep (dense_rank last order by INV_STR_DATE) as POSITION_STOP_DATE
FROM EMPLOYEE_POSITION
GROUP BY X;

Can be written in SQL Server 2012+ as:

SELECT MAX(POSITION_STOP_DATE)
FROM (SELECT ep.*,
             FIRST_VALUE(POS_STOP_DATE) OVER (PARTITION BY X ORDER BY INV_STR_DATE DESC) as POSITION_STOP_DATE
      FROM EMPLOYEE_POSITION ep
     ) ep
GROUP BY x;

In SQL Server 2005+, you can do this with ROW_NUMBER() and conditional aggregation:

SELECT MAX(CASE WHEN seqnum = 1 THEN POS_STOP_DATE END) as POSITION_STOP_DATE)
FROM (SELECT ep.*,
             ROW_NUMBER() OVER (PARTITION BY X ORDER BY INV_STR_DATE DESC) as seqnum
      FROM EMPLOYEE_POSITION ep
     ) ep
GROUP BY x;

Upvotes: 5

Related Questions