Slate uses table triggers to calculate and store a "rank" value on rows in tables that routinely need to be joined into a query where only a single record is desired. Table ranks are recalculated 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 on the most commonly desired sorts.
The table ranking is performed per record, so each record is eligible to have exactly one rank 1 item, exactly one rank 2 item, and so forth. Certain tables rank within specific item types on the record, and this behavior is enumerated in the applicable tables below.
For all tables, the rank criteria order is significant. 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:
- Priority (high, then normal, then low)
- Updated Date (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
For example, if a record has three items, with two being High Priority and the third item being Normal Priority, then using the ranking criteria above, the Normal Priority item would immediately be ranked #3 because 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 different, then the most recently updated item 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 might seem "random," it will only ever be "random" if all preceding sorts contain 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 unstable behavior.
The following plain English explanations describe the situations, with the SQL that generates each rank also provided.
Addresses have two rank columns:
- 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:
- Priority (high, then normal, then low)
- Street has a value (value exists, then no value exists)
- Updated Date (Most recent to least recent)
- Created Date (Most recent to least recent)
- ID (ascending order)
Note: Addresses created through an application submission are stored with a high priority.
Rank overall, also known as Active Address, determines the overall address rank, regardless of the address type. A record can have only one address assigned a rank overall of 1. The ranking order is as follows:
- Highest priority (High, Normal, or Low)
- If addresses have the same priority, then whichever has a street address value is ranked higher
- If addresses have the same priority and have a street address value, the address that is a mailing address will be ranked higher
- If addresses have the same priority and have a street address value, and both are mailing addresses, or both are not mailing addresses, then the most recently updated address is ranked higher
- If addresses have the same priority, have a street address value, and are both mailing addresses or both are not mailing addresses and have the same updated date/time, then the most recently created address will be ranked higher
- If all of these items are the same, then the Active Address will rank by the address ID number (whichever is first alphabetically)
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())Courses have two rank columns:
- 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:
- Semester (ascending)
- Grade Level (prompt order) - If the course has no Grade Level, it is treated as having the order 99.
- Term (prompt order) - If the course has no Term, it is treated as having the order 99.
- Type has a value (value exists, then no value exists)
- ID (ascending)
Rank overall determines the course rank regardless of the school it is associated with. A person record will only have one course with the rank overall set to 1. The ranking order is as follows:
- Semester (ascending)
- Grade Level (prompt order) - If the course has no Grade Level, it is treated as having the order 99.
- Term (prompt order) - If the course has no Term, it is treated as having the order 99.
- Type has a value (value exists, then no value exists)
- 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])Devices have one rank column:
- 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:
- Priority (high, then normal, then low)
- Updated Date (most recent to least recent)
- Created Date (most recent to least recent)
- 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 responses have four rank columns:
- 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:
- Submitted Date (most recent to least recent)
- ID (ascending)
Like rank form, rank form reverse is assigned based on the specific form, and a record can have multiple form responses with a rank form reverse assignment of 1, but just one per form. This rank reverses the order, where the first submitted form response is assigned 1. The ranking order is as follows:
- Submitted Date (oldest/least recent to newest/most recent)
- ID (ascending)
Rank reader is assigned to form responses for forms with the scope Reader. This rank is evaluated among all reader form submissions on the application, so an application will only have one reader form that is assigned 1 as the rank reader. The first reader form submitted for an application will be assigned 1. The ranking order is as follows:
- Submitted Date (oldest/least recent to newest/most recent)
- ID
Rank reader reverse is assigned to form responses for forms with the scope Reader. This rank is evaluated among all reader form submissions on the application, so an application will only have one reader form that is assigned 1 as the rank reader reverse. The most recent reader form submitted for an application will be assigned 1. The ranking order is as follows:
- Submitted Date (most recent to least recent)
- ID
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)Interests have one rank column:
- rank
The ranking order is as follows:
- Order of Importance (ascending)
- Participation Date: To (most recent to least recent). If no To Date is specified, the current date is used.
- 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]Jobs have one rank column:
- rank
The ranking order is as follows:
- Order of Importance (ascending) - only if all Jobs have a defined Order of Importance.
- Employment Date: To (most recent to least recent). If no To Date is specified, the current date is used.
- Participation Date: From (most recent to least recent). If no From Date is specified, the current date is used.
- Job Updated Date (most recent to least recent)
- Job Created Date (most recent to least recent)
- ID (ascending)
Ranking Exclusions:
- None - all jobs are ranked.
The SQL code used to perform the ranking is below:
select j.[id], row_number() over (partition by [record] order by j.[order],
coalesce(j.[to], getdate()) desc, coalesce(j.[from], getdate()) desc,
j.[updated] desc, j.[created] desc, j.[id]) as [rank]Relations have one rank column:
- 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:
- Relationship Type Prompt Order (ascending)
- Relationship Type Value (alphabetical)
- 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])Schools have two rank columns:
- 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:
- Priority (high, then normal, then low)
- Attended Date: To if it exists, otherwise Attended Date: From (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
Note: School records with no level of study are ranked together since they all have the same (blank) level of study.
Rank overall is determined across all schools, regardless of the level of study. A record can only have one school that is assigned rank overall 1. The ranking order is as follows:
- Priority (high, then normal, then low)
- Attended Date: To if it exists, otherwise Attended Date: From (most recent to least recent)
- Created Date (most recent to least recent)
- ID (ascending)
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)Sports have one rank column:
- rank
Column Description
The ranking order is as follows:
- Priority (high, then normal, then low)
- Rating has a value (value exists, then no value exists)
- Rating Prompt Order (ascending)
- Updated Date (most recent to least recent)
- Created Date (most recent to least recent)
- 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)