Posts tagged: T-SQL

Rank function in PL/SQL

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

Sample Data:

user_id email_address email_type activity_date
6437 jim@yahoo.com A 6/21/2009
5271 fred@gmail.com A 6/17/2009
5271 fred12@gmail.com A 6/29/2009
6988 steve@yahoo.com B 6/09/2009
5271 freddy@hotmail.com B 6/26/2009
6988 stephen@comcast.com A 6/22/2009
6437 jimbo@gmail.com B 6/23/2009
5271 fredericko@cantina.com C 6/24/2009

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:

user_id email_address email_type activity_date rnk
6437 jim@yahoo.com A 6/21/2009 1
6437 jimbo@gmail.com B 6/23/2009 2
5271 fred12@gmail.com A 6/29/2009 1
5271 fred@gmail.com A 6/17/2009 2
5271 freddy@hotmail.com B 6/26/2009 3
5271 fredericko@cantina.com C 6/24/2009 4
6988 stephen@comcast.com A 6/22/2009 1
6988 steve@yahoo.com B 6/09/2009 2


(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:

email_address
jim@yahoo.com
fred12@gmail.com
stephen@comcast.com

Panorama theme by Themocracy