Determination of Table Ranks

Slate uses table triggers to calculate and store a 'rank' value on rows in tables that routinely need to be joined in to a query where only a single record is desired. For example, you may wish to view the information about each person's highest GRE score. Table rankings are used to efficiently determine the highest score, without needing to compute this each time the data is accessed. Table ranks are re-calculated for the entire record any time any of the record's objects on that table are modified.

Some tables have multiple 'rank' columns, and the behavior associated with each rank will vary based upon what we have observed to be the most commonly-desired sorts. 

The ranking for all tables is performed per record, so each record is eligible to have exactly one rank 1 item, exactly one rank 2 item, etc. There are certain tables that rank within specific item types on the record, and this behavior is enumerated for the applicable tables below.

For all tables, the order of the rank criteria matters. Ranking criteria after the first item are used only in the case of a tie. For example, a hypothetical table might have this ranking order:

  1. Priority (high, then normal, then low)
  2. Updated Date (most recent to least recent)
  3. Created Date (most recent to least recent)
  4. ID (ascending)

If a record has three items where two are High Priority and the third is Normal Priority, then using the ranking criteria above, the Normal Priority item would immediately be ranked #3 since the other two items both have a higher priority. The two High Priority items would then have their Updated Dates compared. If the Updated Dates are not the same, then the item that was updated most recently would be ranked #1, and the other would be #2. If the Updated Dates are the same, then the Created Dates would be compared, etc.

You will often see that the last order by column is simply [id]. While this may seem to be 'random', it will only ever be 'random' if all preceding sorts contain exactly the same value, and it will only ever be 'random' within a group of records. Without this column, the sort would be truly random and might result in the changing of ranking upon any change to the record, resulting in an unstable behavior.

 Below, we have included the plain English explanations along with the SQL that generates each rank.

Address

Addresses have two rank columns:

  • rank
  • rank_overall
Rank Rank Overall

Rank determines the address rank based on the address type. A record can have both a rank 1 mailing address and a rank 1 permanent address. The ranking order is as follows:

  1. Priority (high, then normal, then low)
  2. Street has a value (value exists, then no value exists)
  3. Updated Date (Most recent to least recent)
  4. Created Date (Most recent to least recent)
  5. ID (ascending order)

Note: Addresses recorded via an application submission are stored with a high priority.

Ranking Exclusions:

  • Addresses with an inactive priority are not ranked.
  • Addresses with an effective date ('Valid From Date') in the future are not ranked.
  • Addresses with an expires date ('Valid Until Date') in the past are not ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by [record], [type] order by [priority] desc, (case when ([street] is null) then 1 else 0 end), [updated] desc, [created] desc, [id]) as [rank],


row_number() over (partition by [record] order by [priority] desc, (case when ([street] is null) then 1 else 0 end), (case when ([type] is null) then 0 else 1 end), [updated] desc, [created] desc, [id]) as [rank_overall]
from [address] with (index([record, type, rank]))
where ([quality] < 3)
and (isnull([priority], 0) > -2)
and (coalesce([expires], getdate()) >= getdate())
and (coalesce([effective], getdate()) <= getdate())
Application

Applications have two rank columns:

  • rank
  • rank_submitted
Rank Rank Submitted

Rank determines the application rank for applications on the record, regardless of the submission status. The ranking order is as follows:

  1. Priority (high, then normal, then low)
  2. Submitted Date if it exists, or Created Date for applications that are not submitted (most recent to least recent)

Ranking Exclusions:

  • Applications with an inactive priority are not ranked at all.
  • Applications in a round configured as Protected from Rank are not ranked at all.
  • Applications that have not been submitted receive a rank, but not a rank_submitted.

The SQL code used to perform the ranking is below:

(case when (r.[protected] = 0) and (a.[priority] > -2) then row_number() over (partition by a.[person], r.[protected] order by a.[priority] desc, coalesce(a.[submitted], a.[created]) desc) end) as [rank],


(case when (r.[protected] = 0) and (a.[priority] > -2) and (a.[submitted] is not null) then row_number() over (partition by a.[person], r.[protected], (case when (a.[submitted] is not null) then 1 end) order by a.[submitted] desc) end) as [rank_submitted]
from [application] a
inner join [lookup.round] r on (r.[id] = a.[round])
Application Reference

Application References have three rank columns:

  • rank
  • rank_type
  • rank_submitted
Rank Rank Type Rank Submitted

Rank determines the application reference rank for application references on the record, regardless of the submission status or type. The ranking order is as follows:

  1. Last Name (alphabetical order)
  2. First Name (alphabetical order)
  3. ID (ascending)

Ranking Exclusions:

  • Application references that have been excluded are not ranked at all.
  • Application references that have not been submitted receive a rank and a rank_type, but not a rank_submitted.

The SQL code used to perform the ranking is below:

select row_number() over (partition by [application] order by [last], [first], [id]) as [rank],

row_number() over (partition by [application], [type] order by [last], [first], [id]) as [rank_type],

(case when ([submitted] is not null) then row_number() over (partition by [application], (case when ([submitted] is null) then 0 else 1 end) order by [submitted], [last], [first], [id]) end) as [rank_submitted]
from [application.reference]
where ([exclude] = 0)
Course

Courses have two rank columns:

  • rank
  • rank_overall
Rank Rank Overall

Rank determines the course rank for each school record. If a person has multiple school records, then each school is eligible to have a rank 1 course. The ranking order is as follows:

  1. Semester (ascending)
  2. Grade Level (prompt order) - If the course has no Grade Level, it is treated as having the order 99.
  3. Term (prompt order) - If the course has no Term, it is treated as having the order 99.
  4. Type has a value (value exists, then no value exists)
  5. ID (ascending)

Ranking Exclusions:

  • None - All courses are ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by c.[record], c.[school] order by c.[semester], (case when (c.[grade_level] is not null) then p.[order] else 99 end), (case when (c.[term] is not null) then pt.[order] else 99 end), (case when (c.[type] is null) then 1 else 0 end), c.[id]) as [rank],


row_number() over (partition by c.[record] order by c.[semester], (case when (c.[grade_level] is not null) then p.[order] else 99 end), (case when (c.[term] is not null) then pt.[order] else 99 end), (case when (c.[type] is null) then 1 else 0 end), c.[id]) as [rank_overall]
from [course] c
left outer join [lookup.prompt] p on (p.[id] = c.[grade_level])
left outer join [lookup.prompt] pt on (pt.[id] = c.[term])
Decision

Decisions have four rank columns:

  • rank
  • rank_reverse
  • rank_confirmed
  • rank_released
Rank Rank Reverse Rank Confirmed Rank Released

Rank uses the confirmed or created dates to order the decisions from most recent to least recent. The ranking order is as follows:

  1. Confirmed Date if it exists, or Created Date for decisions that are not confirmed (most recent to least recent)
  2. ID (ascending)

Ranking Exclusions:

  • Decisions with the type Correspondence/Letter are not ranked at all.
  • Only decisions with a confirmed date receive a rank confirmed.
  • Only decisions with a released date receive a rank released.

The SQL code used to perform the ranking is below:

select (case when (l.[innert] = 0) then row_number() over (partition by d.[application], l.[innert] order by coalesce(d.[confirmed], d.[created]) desc, d.[id]) end) as [rank],

(case when (l.[innert] = 0) then row_number() over (partition by d.[application], l.[innert] order by coalesce(d.[confirmed], d.[created]), d.[id]) end) as [rank_reverse],

(case when (l.[innert] = 0) and (d.[confirmed] is not null) then row_number() over (partition by d.[application], l.[innert], (case when (d.[confirmed] is null) then 0 else 1 end) order by d.[confirmed] desc, d.[id]) end) as [rank_confirmed],

(case when (l.[innert] = 0) and (d.[released] is not null) then row_number() over (partition by d.[application], l.[innert], (case when (d.[released] is null) then 0 else 1 end) order by d.[released] desc, d.[id]) end) as [rank_released]

from [decision] d
inner join [lookup.decision] l on (l.[id] = d.[code])
Device

Devices have one rank column:

  • rank
Rank

Rank is determined based on the device type. A record can have multiple rank 1 devices, but just one per device type. For example, if a person has two email addresses and a primary phone number, one email address will be assigned rank 1, one primary phone number will be assigned rank 1, and the other email address will be assigned rank 2. The ranking order is as follows:

  1. Priority (high, then normal, then low)
  2. Updated Date (most recent to least recent)
  3. Created Date (most recent to least recent)
  4. ID (ascending)

Ranking Exclusions:

  • Devices with an inactive priority are not ranked.

The SQL code used to perform the ranking is below:

select (case when ([priority] != -2) then row_number() over (partition by [record], [type], (case when ([priority] = -2) then 0 else 1 end) order by [priority] desc, [updated] desc, [created] desc, [id]) end) as [rank]
from [device]
Form Response

Form responses have four rank columns:

  • rank_form
  • rank_form_reverse
  • rank_reader
  • rank_reader_reverse
Rank Form Rank Form Reverse Rank Reader Rank Reader Reverse

Rank form is assigned based on the specific form. A record can have multiple form responses with a rank form assignment of 1, but just one per form. The form response will only have a rank form assignment greater than 1 if the person submitted a registration for the same form multiple times. The ranking order is as follows:

  1. Submitted Date (most recent to least recent)
  2. ID (ascending)

Ranking Exclusions:

  • Form responses that have not been submitted are not ranked at all.
  • Only form responses for forms with the scope Reader are assigned a rank reader and a rank reader reverse.

The SQL code used to perform the ranking is below:

select row_number() over (partition by r.[record] order by r.[submitted], r.[id]) as [rank_reader],


row_number() over (partition by r.[record] order by r.[submitted] desc, r.[id] desc) as [rank_reader_reverse]
from [form.response] r
inner join [form] f on (f.[id] = r.[form])
where (f.[scope] = 'reader') and (r.[active] = 1)


select row_number() over (partition by [record], [form] order by [submitted], [id]) as [rank_form],

row_number() over (partition by [record], [form] order by [submitted] desc, [id] desc) as [rank_form_reverse]
from [form.response]
where ([active] = 1)
Interest

Interests have one rank column:

  • rank
Rank

The ranking order is as follows:

  1. Order of Importance (ascending)
  2. Participation Date: To (most recent to least recent). If no To Date is specified, the current date is used.
  3. ID (ascending)

Ranking Exclusions:

  • None - all interests are ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by [record] order by [order], coalesce([to], getdate()) desc, [id]) as [rank]
from [interest]
Job

Jobs have one rank column:

  • rank
Rank

The ranking order is as follows:

  1. Order of Importance (ascending) - only if all Interests have a defined Order of Importance.
  2. Employment Date: To (most recent to least recent). If no To Date is specified, the current date is used.
  3. ID (ascending)

Ranking Exclusions:

  • None - all jobs are ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by [record] order by [order], coalesce([to], getdate()) desc, [id]) as [rank]
from [job]
Relation

Relations have one rank column:

  • rank_category
Rank Category

Rank category is determined within each relation category. A record can have multiple rank category 1 relations, but just one per relation type category. Categories are configured on the relationship_type prompt. For example, if a person has a mother and a father, and both of these relationship types have the category parent, then one will be assigned 1 and the other will be assigned 2. If this person has one other relation that is a sister, and the category for sister is configured as sibling, then that relation record will be assigned 1. The ranking order is as follows:

  1. Relationship Type Prompt Order (ascending)
  2. Relationship Type Value (alphabetical)
  3. ID (ascending)

Note: Relations that do not have a category will all be ranked together. Using the example above, if categories were not assigned to the prompts, then the mother, father, and sister would be assigned rank categories 1, 2, and 3 based on the ranking order above, since they all share the same (blank) category.

Ranking Exclusions:

  • None - all relations are ranked.

The SQL code used to perform the ranking is below:

select r.[id], row_number() over (partition by r.[record], pt.[category] order by pt.[order], pt.[value], r.[id]) as [rank_category]
from [relation] r
left outer join [lookup.prompt] pt on (pt.[id] = r.[type])
School

Schools have two rank columns:

  • rank
  • rank_overall
Rank Rank Overall

Rank is determined based on the school's level of study. A record can have multiple rank 1 schools, but just one per level of study. For example, if a person has two high schools and an undergraduate school, one high school will be assigned rank 1, one undergraduate school will be assigned rank 1, and the other high school will be assigned rank 2. The ranking order is as follows:

  1. Priority (high, then normal, then low)
  2. Attended Date: To if it exists, otherwise Attended Date: From (most recent to least recent)
  3. Created Date (most recent to least recent)
  4. ID (ascending)

Note: School records with no level of study are ranked together, since they all have the same (blank) level of study.

Ranking Exclusions:

  • Schools with an inactive priority are not ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by s.[record], s.[type] order by s.[priority] desc, isnull(s.[to], s.[from]) desc, s.[created] desc, s.[id]) as [rank],

row_number() over (partition by s.[record] order by s.[priority] desc, isnull(s.[to], s.[from]) desc, s.[created] desc, s.[id]) as [rank_overall]
from [school] s
where (isnull(s.[priority], 0) > -2)
School Report

School reports have one rank column:

  • rank
Rank

The ranking order is as follows:

  1. Last Name (alphabetical order)
  2. First Name (alphabetical order)
  3. ID (ascending)

Ranking Exclusions:

  • None - all school reports are ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by [application] order by [last], [first], [id]) as [rank]
from [school.report]
Sport

Sports have one rank column:

  • rank

Column Description

Rank

The ranking order is as follows:

  1. Priority (high, then normal, then low)
  2. Rating has a value (value exists, then no value exists)
  3. Rating Prompt Order (ascending)
  4. Updated Date (most recent to least recent)
  5. Created Date (most recent to least recent)
  6. ID (ascending)

Ranking Exclusions:

  • Sports with an inactive priority are not ranked.

The SQL code used to perform the ranking is below:

select row_number() over (partition by sp.[record] order by [priority] desc, (case when (sp.[rating] is null) then 1 else 0 end), r.[order], sp.[updated] desc, sp.[created] desc, sp.[id]) as [rank]

from [sport] sp
left outer join [lookup.prompt] r on (r.[id] = sp.[rating])
where (sp.[priority] > -2)
Test

Tests have 57 rank columns, but there are three basic types:

  • rank
  • rank_confirmed
  • rank_overall

For all test score ranks, rank is determined among test scores with the same test type. There are also subtype ranks, which further divide the scores based on the test's subtype. For example, if the person has two AP scores: one AP Biology score and one AP Chemistry score, the rank would assign just one of those scores 1 and the other would be assigned 2. Since Chemistry and Biology are two different subtypes of the AP test type, both of these would be assigned 1 as the rank_subtype.

Rank Rank Confirmed Rank Overall

The rank calculation considers both verified and self-reported test scores; however, it prioritizes verified scores during the calculation. There are 19 different ranks that use this type of ranking. When referring to score below, the specific score that is used depends on which rank is being calculated. The rank and rank_subtype ranks look at the total column. There are 17 subscore columns, which are also ranked, such as rank_score1 and rank_score2. For these ranks, the specified subscore is used. 

The ranking order for rank and rank_subtype is as follows:

  1. Test Status (verified first, then self-reported)
  2. Score (highest to lowest)
  3. Sum of score 1, score 2, score 3, and score 4 (highest to lowest)
  4. Test Date (most recent to least recent)
  5. ID (ascending)

The ranking order for rank_score[1-17] is as follows:

  1. Test Status (verified first, then self-reported)
  2. Score (highest to lowest)
  3. Test Date (most recent to least recent)
  4. ID (ascending)

Ranking Exclusions:

  • Cancelled test scores are not ranked.
  • Optional test scores are not ranked.
  • Test scores with the status Discrepancy are not ranked.
  • Only test scores that are verified are assigned rank_confirmed values.
  • Only test scores associated with test types that have subtypes are assigned rank_subtype, rank_overall_subtype, and rank_confirmed_subtype values.

The SQL code used to perform the ranking is below:

select row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank],

(case when ([score1] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score1] desc, [date] desc, [id]) end) as [rank_score1],

(case when ([score2] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score2] desc, [date] desc, [id]) end) as [rank_score2],

(case when ([score3] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score3] desc, [date] desc, [id]) end) as [rank_score3],

(case when ([score4] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score4] desc, [date] desc, [id]) end) as [rank_score4],

(case when ([score5] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score5] desc, [date] desc, [id]) end) as [rank_score5],

(case when ([score6] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score6] desc, [date] desc, [id]) end) as [rank_score6],

(case when ([score7] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score7] desc, [date] desc, [id]) end) as [rank_score7],

(case when ([score8] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score8] desc, [date] desc, [id]) end) as [rank_score8],

(case when ([score9] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score9] desc, [date] desc, [id]) end) as [rank_score9],

(case when ([score10] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score10] desc, [date] desc, [id]) end) as [rank_score10],

(case when ([score11] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score11] desc, [date] desc, [id]) end) as [rank_score11],

(case when ([score12] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score12] desc, [date] desc, [id]) end) as [rank_score12],

(case when ([score13] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score13] desc, [date] desc, [id]) end) as [rank_score13],

(case when ([score14] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score14] desc, [date] desc, [id]) end) as [rank_score14],

(case when ([score15] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score15] desc, [date] desc, [id]) end) as [rank_score15],

(case when ([score16] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score16] desc, [date] desc, [id]) end) as [rank_score16],

(case when ([score17] is not null) then row_number() over (partition by [record], [type] order by (case when ([confirmed] = 1) then 0 else 1 end), [score17] desc, [date] desc, [id]) end) as [rank_score17]
from [test]
where ([cancelled] is null) and ([optional] = 0) and (coalesce([confirmed], -1) != 0)

select row_number() over (partition by [record], [type] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_confirmed],

(case when ([score1] is not null) then row_number() over (partition by [record], [type] order by [score1] desc, [date] desc, [id]) end) as [rank_confirmed_score1],

(case when ([score2] is not null) then row_number() over (partition by [record], [type] order by [score2] desc, [date] desc, [id]) end) as [rank_confirmed_score2],

(case when ([score3] is not null) then row_number() over (partition by [record], [type] order by [score3] desc, [date] desc, [id]) end) as [rank_confirmed_score3],

(case when ([score4] is not null) then row_number() over (partition by [record], [type] order by [score4] desc, [date] desc, [id]) end) as [rank_confirmed_score4],

(case when ([score5] is not null) then row_number() over (partition by [record], [type] order by [score5] desc, [date] desc, [id]) end) as [rank_confirmed_score5],

(case when ([score6] is not null) then row_number() over (partition by [record], [type] order by [score6] desc, [date] desc, [id]) end) as [rank_confirmed_score6],

(case when ([score7] is not null) then row_number() over (partition by [record], [type] order by [score7] desc, [date] desc, [id]) end) as [rank_confirmed_score7],

(case when ([score8] is not null) then row_number() over (partition by [record], [type] order by [score8] desc, [date] desc, [id]) end) as [rank_confirmed_score8],

(case when ([score9] is not null) then row_number() over (partition by [record], [type] order by [score9] desc, [date] desc, [id]) end) as [rank_confirmed_score9],

(case when ([score10] is not null) then row_number() over (partition by [record], [type] order by [score10] desc, [date] desc, [id]) end) as [rank_confirmed_score10],

(case when ([score11] is not null) then row_number() over (partition by [record], [type] order by [score11] desc, [date] desc, [id]) end) as [rank_confirmed_score11],

(case when ([score12] is not null) then row_number() over (partition by [record], [type] order by [score12] desc, [date] desc, [id]) end) as [rank_confirmed_score12],

(case when ([score13] is not null) then row_number() over (partition by [record], [type] order by [score13] desc, [date] desc, [id]) end) as [rank_confirmed_score13],

(case when ([score14] is not null) then row_number() over (partition by [record], [type] order by [score14] desc, [date] desc, [id]) end) as [rank_confirmed_score14],

(case when ([score15] is not null) then row_number() over (partition by [record], [type] order by [score15] desc, [date] desc, [id]) end) as [rank_confirmed_score15],

(case when ([score16] is not null) then row_number() over (partition by [record], [type] order by [score16] desc, [date] desc, [id]) end) as [rank_confirmed_score16],

(case when ([score17] is not null) then row_number() over (partition by [record], [type] order by [score17] desc, [date] desc, [id]) end) as [rank_confirmed_score17]
from [test]
where ([cancelled] is null) and ([optional] = 0) and ([confirmed] = 1)

select row_number() over (partition by [record], [type] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_overall],

(case when ([score1] is not null) then row_number() over (partition by [record], [type] order by [score1] desc, [date] desc, [id]) end) as [rank_overall_score1],

(case when ([score2] is not null) then row_number() over (partition by [record], [type] order by [score2] desc, [date] desc, [id]) end) as [rank_overall_score2],

(case when ([score3] is not null) then row_number() over (partition by [record], [type] order by [score3] desc, [date] desc, [id]) end) as [rank_overall_score3],

(case when ([score4] is not null) then row_number() over (partition by [record], [type] order by [score4] desc, [date] desc, [id]) end) as [rank_overall_score4],

(case when ([score5] is not null) then row_number() over (partition by [record], [type] order by [score5] desc, [date] desc, [id]) end) as [rank_overall_score5],

(case when ([score6] is not null) then row_number() over (partition by [record], [type] order by [score6] desc, [date] desc, [id]) end) as [rank_overall_score6],

(case when ([score7] is not null) then row_number() over (partition by [record], [type] order by [score7] desc, [date] desc, [id]) end) as [rank_overall_score7],

(case when ([score8] is not null) then row_number() over (partition by [record], [type] order by [score8] desc, [date] desc, [id]) end) as [rank_overall_score8],

(case when ([score9] is not null) then row_number() over (partition by [record], [type] order by [score9] desc, [date] desc, [id]) end) as [rank_overall_score9],

(case when ([score10] is not null) then row_number() over (partition by [record], [type] order by [score10] desc, [date] desc, [id]) end) as [rank_overall_score10],

(case when ([score11] is not null) then row_number() over (partition by [record], [type] order by [score11] desc, [date] desc, [id]) end) as [rank_overall_score11],

(case when ([score12] is not null) then row_number() over (partition by [record], [type] order by [score12] desc, [date] desc, [id]) end) as [rank_overall_score12],

(case when ([score13] is not null) then row_number() over (partition by [record], [type] order by [score13] desc, [date] desc, [id]) end) as [rank_overall_score13],

(case when ([score14] is not null) then row_number() over (partition by [record], [type] order by [score14] desc, [date] desc, [id]) end) as [rank_overall_score14],

(case when ([score15] is not null) then row_number() over (partition by [record], [type] order by [score15] desc, [date] desc, [id]) end) as [rank_overall_score15],

(case when ([score16] is not null) then row_number() over (partition by [record], [type] order by [score16] desc, [date] desc, [id]) end) as [rank_overall_score16],

(case when ([score17] is not null) then row_number() over (partition by [record], [type] order by [score17] desc, [date] desc, [id]) end) as [rank_overall_score17]
from [test]
where ([cancelled] is null) and ([optional] = 0) and (coalesce([confirmed], -1) != 0)

select row_number() over (partition by [record], [type], [subtype] order by (case when ([confirmed] = 1) then 0 else 1 end), [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_subtype],

row_number() over (partition by [record], [type], [subtype] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_overall_subtype]
from [test]
where ([cancelled] is null) and ([optional] = 0) and (coalesce([confirmed], -1) != 0) and ([subtype] is not null)


select row_number() over (partition by [record], [type], [subtype] order by [total] desc, [score1] + coalesce([score2], 0) + coalesce([score3], 0) + coalesce([score4], 0) desc, [date] desc, [id]) as [rank_confirmed_subtype]
from [test]
where ([cancelled] is null) and ([optional] = 0) and ([confirmed] = 1) and ([subtype] is not null)
Was this article helpful?
38 out of 38 found this helpful