sql - Ranking query records in specific order - Stack Overflow

Assume we have Ambulance with Patient reception, and each Patient need to do one or more (min. 1, max. 4) blood analysis. Each Patient gets OrdNumber (by arrival) FOR THAT DAY, but later they are prioritized in a way that patients with more required analysis are before patients with fewer required analysis (ALSO ON PER DAY BASIS).

Next day OrdNumbers start from 1 again.

Let's say:

• Day1 we have 2 patients: Patient_Id=1 with 3 analysis, Patient_Id=2 with 4 analysis.
• Day2 we have 2 patients: Patient_Id=3 with 4 analysis, Patient_Id=4 with 2 analysis.

Table Reception will look like this:

ReceptionId	DayOfWeek	PatientId	Patient_OrdNumber_ForThatDay	NumberOfRequiredAnalysis	Patient_OrdNumber_ByNumberOfRequiredAnalysis
1	1	1	1	3	2
2	1	2	2	4	1
3	2	3	1	4	1
4	2	4	2	2	2

Assume we have Ambulance with Patient reception, and each Patient need to do one or more (min. 1, max. 4) blood analysis. Each Patient gets OrdNumber (by arrival) FOR THAT DAY, but later they are prioritized in a way that patients with more required analysis are before patients with fewer required analysis (ALSO ON PER DAY BASIS).

Next day OrdNumbers start from 1 again.

Let's say:

• Day1 we have 2 patients: Patient_Id=1 with 3 analysis, Patient_Id=2 with 4 analysis.
• Day2 we have 2 patients: Patient_Id=3 with 4 analysis, Patient_Id=4 with 2 analysis.

Table Reception will look like this:

ReceptionId	DayOfWeek	PatientId	Patient_OrdNumber_ForThatDay	NumberOfRequiredAnalysis	Patient_OrdNumber_ByNumberOfRequiredAnalysis
1	1	1	1	3	2
2	1	2	2	4	1
3	2	3	1	4	1
4	2	4	2	2	2

Records in Analysis table are ordered by NumberOfRequiredAnalysis (same as Patient_OrdNumber_ByNumberOfRequiredAnalysis) FOR THAT DAY, so we will have:

AnalysisId	ReceptionId
1	2
2	2
3	2
4	2
5	1
6	1
7	1
8	3
9	3
10	3
11	3
12	4
13	4

So, we have all analysis grouped by ReceptionId, but not ordered by ReceptionId.

What I need to get is ordinal number (for coloring each odd group rows on report) of each ReceptionId group from the Analysis table, like this:

AnalysisId	OrdinalNumerOfReceptionIdGroup
1	1
2	1
3	1
4	1
5	2
6	2
7	2
8	3
9	3
10	3
11	3
12	4
13	4

I tried to achieve this with DENSE_RANK() :

DENSE_RANK() OVER 
    (PARTITION BY Reception.DayOfWeek, Reception.Patient_OrdNumber_ByNumberOfRequiredAnalysys 
     ORDER BY Analysis.AnalysisId) AS OrdinalNumerOfReceptionIdGroup

but that does not produce the desired output.

• sql
• sql-server
• partitioning
• dense-rank

Share Improve this question Follow edited Jan 4 at 21:39 Dale K 27.6k1515 gold badges5858 silver badges8383 bronze badges asked Jan 4 at 20:13 CondottieroCondottiero 111 silver badge 3

• Typo alert: it's an analysis - not "analysys" ..... – marc_s Commented Jan 4 at 20:52
• Your desired results don't appear to take into account which day they are on, they just seem to match the order of records in your Analysis table. Is something missing there? Or you handle that when creating the Analysis rows? – Dale K Commented Jan 4 at 21:41
• @Condottiero how did you get on? You have 2 possible answers below – Dale K Commented Jan 7 at 0:37

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Given your Analysis table exists as shown, you can assign the OrdinalNumerOfReceptionIdGroup value as follows:

1. Get each group transition using lag to compare the current ReceptionId to the previous ReceptionId. Lets calls this ChangeInGroup.
2. Sum the ChangeInGroup group value for the current row and all preceding rows.

with cte as (
  select *
    , case when ReceptionId <> lag (ReceptionId, 1, ReceptionId) over (order by AnalysisId) then 1 else 0 end ChangeInGroup
  from Analysis
)
select AnalysisId,  ReceptionId
  , sum(ChangeInGroup) over
    (order by AnalysisId rows between unbounded preceding and current row) + 1
from cte
order by AnalysisId;

Which gives as requested:

AnalysisId	ReceptionId	OrdinalNumerOfReceptionIdGroup
1	2	1
2	2	1
3	2	1
4	2	1
5	1	2
6	1	2
7	1	2
8	3	3
9	3	3
10	3	3
11	3	3
12	4	4
13	4	4

db<>fiddle

You can use ROW_NUMBER() to get an ordinal number based on DayOfWeek and Patient_OrdNumber_ByNumberOfRequiredAnalysis, this is done in CTE ordered_receptions, then it is joined to the analysis table to map the ordinal number to each analysis.

Fiddle

WITH ordered_receptions AS (
    SELECT 
        ReceptionId,
        ROW_NUMBER() OVER (ORDER BY DayOfWeek, Patient_OrdNumber_ByNumberOfRequiredAnalysis) AS ordinalnumber
    FROM 
        reception
)
, mapped_analysis AS (
    SELECT 
        a.AnalysisId,
        r.ordinalnumber
    FROM 
        analysis a
    JOIN 
        ordered_receptions r ON a.receptionid = r.receptionid
)
SELECT 
    analysisid,
    ordinalnumber AS ordinalnumer_receptionid_group
FROM 
    mapped_analysis
ORDER BY 
    analysisid;

Output

analysisid	ordinalnumer_receptionid_group
1	1
2	1
3	1
4	1
5	2
6	2
7	2
8	3
9	3
10	3
11	3
12	4
13	4