Cardinals and Ordinals

As a child, I learned about cardinal and ordinal numbers. I just checked Wikipedia and found that my understanding of them is … childlike. I remember cardinal numbers as non-negative integers; 0, 1, 2, 3, and so on. I remember ordinal numbers as first, second, third, and so on.

The difference is as follows. Suppose we have ten numbers listed from smallest to largest; 2, 3, 5, 7, 11, 13, 17, 19, 23, 29. (Can you guess how I chose them?) The first number is 2. The second number is 3. The ninth number is 23.

SQL for Ordinals

Imagine a table of ten rows where one of the columns has these ten values. What is the SQL to identify the ordinal number associated with each cardinal number?

select VALUE, ROWNUM from PS_INTEGERS
where IS_PRIME = ‘Y’
order by VALUE

That’s simple enough, but what if there are multiple rows with the same value? In my new business, Pee Wee Bees, I track each visit by each child. I update a “leader” board each day with the children who have visited my indoor playground the most. As of October 5, 2012, I had these results:

• First place: one child with 57 visits
• Second place: two children tied at 37 visits each
• Third place: one child with 33 visits
• Fourth place: two children tied at 29 visits each

And so on, up to tenth place.

A Self-Joined View for Ordinals

I created a view in SQL Server as follows:

create view PWB_NBR_VISITS
as (select CHILD_FIRST_NAME,
CHILD_LAST_NAME,
max(VISIT_DATE) as LAST_VISIT,
count(*) as NBR_VISITS
from PWB_VISITS
group by CHILD_FIRST_NAME, CHILD_LAST_NAME)

This view generates one row for each child by grouping rows in a table that contains each visit. It contains the children’s first and last names as keys. It uses aggregate functions max() and count() to give me the date of the last visit and the number of visits. I used the view as follows:

select PNV1.CHILD_FIRST_NAME, PNV1.CHILD_LAST_NAME, PNV1.NBR_VISITS, count(distinct PNV2.NBR_VISITS) as RANKING,
(case when PNV1.LAST_VISIT > getdate() - 7 then 'UP' else '' end) as CHANGE
from PWB_NBR_VISITS PNV1, PWB_NBR_VISITS PNV2
where PNV2.NBR_VISITS >= PNV1.NBR_VISITS
group by PNV1.CHILD_FIRST_NAME, PNV1.CHILD_LAST_NAME, PNV1.NBR_VISITS, PNV1.LAST_VISIT
order by PNV1.NBR_VISITS desc, PNV1.CHILD_FIRST_NAME, PNV1.CHILD_LAST_NAME

I join the view with itself to calculate the RANKING or ordinal number. The ranking is the (not exactly) number of rows in PNV2 that have NBR_VISITS greater than or equal to the NBR_VISITS of the row in PNV1. Remember my data:

57, 37, 37, 33, 29, 29, 27, 25, 25, 22, 22, 22, 22, 22, 21, 21, 21, 20, 202, 20, 18, 18, 18, 18, 18, 18 …

There is one row whose NBR_VISITS is >= 57, so that row has RANKING = 1.

There are three rows whose NBR_VISITS is >= 37, but we want to describe our “37” rows as being tied for second place, not tied for third place. That’s why the SQL statement uses “count(distinct PNV2.NBR_VISITS).” The distinct keyword tells the database server to count the two “37” rows as one because they have the same value for PNV2.NBR_VISITS. That gives both children a RANKING = 2.

Similarly, there are six rows whose NBR_VISITS is >= 29, but “distinct” makes RANKING = 4.

Highlighting Recency

Is “recency” a word? I’m looking for the noun that corresponds to the adjective “recent.”

This project was designed to acknowledge and thank my most frequent customers. I “awarded” an “up arrow” from the Wingdings font and bright red characters to the children who had accumulated the most visits and also visited Pee Wee Bees within the past week. I identified them by adding “max(VISIT_DATE)” to the view and the CHANGE field to the SQL statement.

(case when PNV1.LAST_VISIT > getdate() - 7 then 'UP' else '' end) as CHANGE