SQLServer equivalent of SELECT RANK ... OVER ... PARTITION in Oracle

  • I had some code that was running like a three legged dog with three broken legs. All our systems work on Oracle & SQLServer with specific code inserted where necessary for the relevant DB. Here's the code as originally written. The table names and variable names aren't important, but there are about about 1.5m records in both O_950P & O_950D. There are suitable indices on O_950D for the selections being made.

    SELECT A.PAGE_TOT ALPHA_VAR1,

    A.SUB_ACC_ID ALPHA_VAR2,

    A.STAT_DATE ALPHA_VAR3,

    A.TYPE ALPHA_VAR4,

    A.PAGE_NO ALPHA_VAR5,

    A.CL_BAL ALPHA_VAR6

    FROM O_950P A,

    O_950D C

    WHERE C.N_ACC_ID = 'xxx'

    AND A.N_ORIGIN = C.N_ORIGIN

    AND A.SUB_ACC_ID = C.SUB_ACC_ID

    AND A.STAT_DATE = C.STAT_DATE

    AND A.PAGE_TOT = C.LAST_PAGE_TOT

    AND A.PAGE_NO = C.LAST_PAGE_NO

    AND A.TYPE = C.TYPE

    AND C.STAT_DATE = (SELECT MAX(D.STAT_DATE)

    FROM O_950D D

    WHERE D.N_ORIGIN = A.N_ORIGIN

    AND D.SUB_ACC_ID = A.SUB_ACC_ID

    AND D.TYPE = A.TYPE

    );

    Rewriting it in Oracle as this improved the performance about five fold:

    WITH

    RANKED_PAGES AS

    (SELECT RANK() OVER (PARTITION BY P.N_ORIGIN,

    P.SUB_ACC_ID,

    P.TYPE

    ORDER BY P.STAT_DATE DESC,

    P.PAGE_TOT,

    P.PAGE_NO) RK,

    P.*

    FROM O_950P P,

    O_950SUB S

    WHERE S.N_ACC_ID = 'xxx'

    AND P.N_ORIGIN = S.SUB_ACC_ORIGIN

    AND P.SUB_ACC_ID = S.SUB_ACC_ID

    )

    SELECT RANKED_PAGES.PAGE_TOT ALPHA_VAR1,

    RANKED_PAGES.SUB_ACC_ID ALPHA_VAR2,

    RANKED_PAGES.STAT_DATE ALPHA_VAR3,

    RANKED_PAGES.TYPE ALPHA_VAR4,

    RANKED_PAGES.PAGE_NO ALPHA_VAR5,

    RANKED_PAGES.CL_BAL ALPHA_VAR6

    FROM RANKED_PAGES

    WHERE RANKED_PAGES.RK = 1

    ORDER BY RANKED_PAGES.N_ORIGIN,

    RANKED_PAGES.SUB_ACC_ID,

    RANKED_PAGES.TYPE

    ;

    My question is, does anybody know what the SQLServer equivalent of this (RANK ... OVER ... PARTITION) would be, please? (Assuming there one). It needs to run in SQLServer 2008 and, probably, 2005.

  • It's exactly the same:

    http://msdn.microsoft.com/en-us/library/ms176102.aspx

  • Why didn't I think of that? Thank you very much.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply