sql rank only continuous rows

Question

I have a query in which I am ranking the rows on the basis of 3 columns. I am successful in doing so, except that if any row contains same data in those 3 columns, it gives it the next rank even if it is not continuous in the output. I want that if any row matches the data in those columns, it should be given next rank only if it is in continuous rows, and if not then it should again give it rank as 1. I tried the following code:

  SELECT DISTINCT DENSE_RANK () OVER (PARTITION BY Patient_ID, 
                                                 Opnametype, 
                                                 afdelingscode ORDER BY  Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd) AS rnk, 
                *
  FROM t_opnames
  ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd

It is giving the output as:

rnk Opnamenummer Patient_ID afdelingscode     Opnametype   Specialismen  OntslagDatumTijd ...
1   2983800      100006     RD8-GH MAU        Inpatient-E  GM            2014-09-01 14:50:00.000
2   2983800      100006     RD8-GH MAU        Inpatient-E  GM            2014-09-02 19:32:00.000
1   2983800      100006     RD8-GH Ward 08    Inpatient-E  GM            2014-09-03 17:12:00.000  
1   2983800      100006     RD8-GH Endo       Inpatient-E  GM            2014-09-09 09:06:00.000
2   2983800      100006     RD8-GH Ward 08    Inpatient-E  GM            2014-09-17 17:00:00.000
3   2983800      100006     RD8-GH Ward 08    Inpatient-E  GM            2014-10-01 17:15:00.000

So, all the rows are correct except last 2 rows. I want the rank of them as 1 and 2 instead of 2 and 3, because the row with "RD8-GH Endo" is between them. So how can I do that?

Answer 1

You can a co-related sub query to achieve this. Use something like this

DECLARE @t_opnames TABLE
(
    Opnamenummer INT,
    Patient_ID INT,
    afdelingscode     VARCHAR(100),
    Opnametype   VARCHAR(100),
    Specialismen  CHAR(2),
    OntslagDatumTijd DATETIME
)

Insert into @t_opnames
SELECT  2983800      ,100006,     'RD8-GH MAU',        'Inpatient-E',  'GM',            '2014-09-01 14:50:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH MAU',        'Inpatient-E',  'GM',            '2014-09-02 19:32:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Ward 08',    'Inpatient-E',  'GM',            '2014-09-03 17:12:00.000'  
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Endo',       'Inpatient-E',  'GM',            '2014-09-09 09:06:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Ward 08',    'Inpatient-E',  'GM',            '2014-09-17 17:00:00.000'
UNION ALL SELECT 2983800      ,100006,     'RD8-GH Ward 08',    'Inpatient-E',  'GM',            '2014-10-01 17:15:00.000'


;WITH CTE as 
(
SELECT DENSE_RANK() OVER(ORDER BY  Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd) rnk,* 
  FROM @t_opnames
)
SELECT rnk-ISNULL((
            SELECT MAX(rnk) 
            FROM CTE c2 
            WHERE c2.Opnamenummer <= c1.Opnamenummer
            AND c2.SPECIALISMEN <= c1.SPECIALISMEN
            AND c2.OntslagDatumTijd <= c1.OntslagDatumTijd
            AND c2.rnk < c1.rnk
            AND (c2.Patient_ID <> c1.Patient_ID 
                OR   c2.Opnametype <> c1.Opnametype 
                OR c2.afdelingscode <> c1.afdelingscode)),0) rnk,Patient_ID, Opnametype,afdelingscode,Opnamenummer, SPECIALISMEN, OntslagDatumTijd
FROM CTE c1
  ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd

Answer 2

Finally I got the solution of my query, now I am getting my desired output and that too in 3 seconds running over 75k+ rows. The code I used is:

SELECT DISTINCT ROW_NUMBER () OVER (ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd) AS rownum, 
            * INTO #temp
FROM t_opnames
ORDER BY Patient_ID, Opnamenummer, SPECIALISMEN, Opnametype, OntslagDatumTijd;

WITH CTE
AS (SELECT *, 
           ROW_NUMBER () OVER (ORDER BY rownum) - ROW_NUMBER () OVER (PARTITION BY Patient_ID, 
                                                                                   Opnametype, 
                                                                                   afdelingscode ORDER BY rownum) AS RowGroup
      FROM #temp) 
SELECT ROW_NUMBER () OVER (PARTITION BY RowGroup, 
                                        Patient_ID, 
                                        Opnametype, 
                                        afdelingscode ORDER BY rownum) AS GroupSequence, 
       *
  FROM CTE
  ORDER BY rownum;

DROP TABLE #temp;

I referred an example posted at this page