Advancement 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 into a query where only a single record is desired. 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 on 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. Certain tables rank within specific item types on the record, and this behavior is enumerated in 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)

For example, 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 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 created 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())
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])
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 Jobs 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. Participation Date: From (most recent to least recent). If no From Date is specified, the current date is used.
  4. Job Updated Date (most recent to least recent)
  5. Job Created Date (most recent to least recent) 
  6. 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]
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)
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)
Was this article helpful?
0 out of 0 found this helpful