Sql rank only solid lines

Question

I have a query in which I rank rows based on three columns. I succeed, except that if a row contains the same data in these three columns, it gives the next rank, even if it is not continuous in output. I want that if any row matches the data in these columns, it should be assigned the next rank only if it is in continuous rows, and if not, then it should again give it a rank of 1. I tried the following the 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 outputs 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 lines are correct, except for the last two lines. I want their rank to be 1 and 2 instead of 2 and 3, because the line with "RD8-GH Endo" is between them. So how can I do this?

Answer 1

Finally, I got a solution to my query, now I get my desired result and this also works on 75k + lines after 3 seconds. The code I use 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 gave an example posted on this page

Answer 2

To achieve this, you can link the corresponding query. 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 3

This does not directly answer the question, but I am trying to explain why what you are trying does not work as you expect.

Your problem is caused by PARTITION . If you remove the unique columns from your PARTITION clause, you will be left with afdelingscode . So your PARTITION groups the data like this:

 RD8-GH Endo RD8-GH MAU RD8-GH MAU RD8-GH Ward 08 RD8-GH Ward 08 RD8-GH Ward 08 

The ORDER BY determines the order in your PARTITION , so when you delete the non-historical columns again, you get the ORDER BY OntslagDatumTijd , which produces this where it is ordered by the date column, notes that the sections are still separated by afdelingscode :

 afdelingscode OntslagDatumTijd RD8-GH Endo 2014-09-09 09:06:00.000 RD8-GH MAU 2014-09-01 14:50:00.000 RD8-GH MAU 2014-09-02 19:32:00.000 RD8-GH Ward 08 2014-09-03 17:12:00.000 RD8-GH Ward 08 2014-09-17 17:00:00.000 RD8-GH Ward 08 2014-10-01 17:15:00.000 

Then ranking is applied to these sections. The result of this will be:

 rnk afdelingscode OntslagDatumTijd 1 RD8-GH Endo 2014-09-09 09:06:00.000 1 RD8-GH MAU 2014-09-01 14:50:00.000 2 RD8-GH MAU 2014-09-02 19:32:00.000 1 RD8-GH Ward 08 2014-09-03 17:12:00.000 2 RD8-GH Ward 08 2014-09-17 17:00:00.000 3 RD8-GH Ward 08 2014-10-01 17:15:00.000 

Thus, it is graded according to how you indicated, the problem in your release is that at the end of your selection (unhistorical column removal), the OntslagDatumTijd date column is ordered, which gives you:

 rnk afdelingscode OntslagDatumTijd 1 RD8-GH MAU 2014-09-01 14:50:00.000 2 RD8-GH MAU 2014-09-02 19:32:00.000 1 RD8-GH Ward 08 2014-09-03 17:12:00.000 1 RD8-GH Endo 2014-09-09 09:06:00.000 2 RD8-GH Ward 08 2014-09-17 17:00:00.000 3 RD8-GH Ward 08 2014-10-01 17:15:00.000 

I will continue to look at this if the other answer you sent does not meet your requirements.

Reference:

OVER offer

SECTION . Partitioning the query result set into sections. The window function is applied to each section separately and the calculation is restarted for each section.

ORDER BY clause Defines the logical order of the rows in each section of the result set. That is, it determines the logical order in which the calculation of the window function is performed.

Here's a potential solution that may have performance issues with the size of the data you are using, but you can check it out:

 -- sets up your dummy data CREATE TABLE #t_opnames ( Opnamenummer INT , Patient_ID INT , afdelingscode NVARCHAR(20) , Opnametype NVARCHAR(20) , Specialismen NVARCHAR(20) , OntslagDatumTijd DATETIME ); INSERT INTO #t_opnames ( Opnamenummer, Patient_ID, afdelingscode, Opnametype, Specialismen, OntslagDatumTijd ) VALUES ( 2983800, 100006, 'RD8-GH MAU', 'Inpatient-E', 'GM', '2014-09-01 14:50:00.000' ), ( 2983800, 100006, 'RD8-GH MAU', 'Inpatient-E', 'GM', '2014-09-02 19:32:00.000' ), ( 2983800, 100006, 'RD8-GH Ward 08', 'Inpatient-E', 'GM', '2014-09-03 17:12:00.000' ), ( 2983800, 100006, 'RD8-GH Endo', 'Inpatient-E', 'GM', '2014-09-09 09:06:00.000' ), ( 2983800, 100006, 'RD8-GH Ward 08', 'Inpatient-E', 'GM', '2014-09-17 17:00:00.000' ), ( 2983800, 100006, 'RD8-GH Ward 08', 'Inpatient-E', 'GM', '2014-10-01 17:15:00.000' ) -- I've added a row number to your data to enable iteration over the data SELECT ROW_NUMBER() OVER ( ORDER BY OntslagDatumTijd ) AS rn , * INTO #temp FROM #t_opnames ORDER BY OntslagDatumTijd -- this will iterate over the rows and apply the rankings ;WITH cte AS ( SELECT *, 1 AS rnk FROM #temp WHERE rn = 1 UNION ALL SELECT t.*, CASE WHEN cte.afdelingscode = t.afdelingscode THEN cte.rnk + 1 ELSE 1 END AS rnk FROM #temp t INNER JOIN cte ON cte.rn +1 = t.rn ) SELECT * FROM cte DROP TABLE #t_opnames DROP TABLE #temp 

You will MAXRECURSION into the MAXRECURSION limit for a larger dataset for which you need to change the limit using the following after the final SELECT :

 SELECT * FROM cte OPTION (MAXRECURSION 0) 

Setting this value to 0 will not impose any restrictions; you can set this number to the size of your data set, if you know it.