Here’s some code which demonstrates the usefulness of the Rank function in PL/SQL (can also be used in T-SQL):

Sample Data:

SELECT user_id,
     email_address,
     email_type,
 RANK () OVER (PARTITION BY user_id ORDER BY email_type, activity_date DESC) rnk
FROM email_table

Output:

(Notice on the two records where user_id = 5271 and email_type = ‘A’ the rank of 1 is given to the record with the most current activity_date. This is because the ORDER BY portion of the RANK function sorts the records for each unique user_id first by email_type and second by activity_date. This makes it possible to select the most current record)

And with this code we can grab only the email addresses with a type of ‘A’ and the most current activity_date by using the WHERE rnk = 1 clause:

SELECT email_address FROM
 (SELECT user_id,
       email_address,
       email_type,
  RANK () OVER (PARTITION BY user_id ORDER BY email_type, activity_date DESC) rnk
 FROM email_table)
WHERE rnk = 1;

Output:

I promised Marcus that I would post some content to this blog and be an actual contributor to the blog instead of just a typical blog lurker/voyeur (like I usually am with blogs), so here’s a bit of information about some basic SQL commands that have proved useful to me lately in PL/SQL: count() and sql%rowcount. They can be very helpful when issuing sql statements where you need to keep track of the number of records affected by the statement.

In the procedure I was working on recently I had an UPDATE statement that looked like this:

UPDATE {table1}
SET {column1} = {value1}
WHERE {column1} = {value2}
AND {column2} = {value3};

and I wanted to find out how many records were a) going to be affected by the statement as well as how many records were b) actually affected by the statement when the statement was run.

I set up 2 local variables and initialized them to zero:

count1 number(7) := 0;
count2 number(7) := 0;

then I used this statement to pass the number of records which were going to be affected by the UPDATE statement to count1:

SELECT count(*)
INTO count1
FROM {table1}
WHERE {column1} = {value2}
AND {column2} = {value3};

and directly after the UPDATE statement I used this code to pass the number of records that were actually updated by the statement to count2:

count2 := sql%rowcount;

These 2 variables can now be compared to each other for error handling purposes to make sure the correct number of records were updated, they can be used for display purposes, used in subsequent IF statements or in a variety of other practical ways.

This is basically what the code looked like:
…
IS
count1 number(7) := 0;
count2 number(7) := 0;
BEGIN
SELECT count(*)
INTO count1
FROM {table1}
WHERE {column1} = {value2}
AND {column2} = {value3};

UPDATE {table1}
SET {column1} = {value1}
WHERE {column1} = {value2}
AND {column2} = {value3};
count2 := sql%rowcount;

IF (count1 = count2) THEN
dbms_output.put_line(‘Number of records match!’);
END IF;
…