SQL question ( retrieving records based on a priority orde..

Hi all,

I have a table with this structure:

Customer ID | Transaction date | Transaction type
1 | 1/2/2008 | F
1 | 1/4/2007 | M
1 | 1/2/2008 | R
2 | 1//5/2007 | M
2 | 1/6/2007 | R


For each customer ID, I need to retrive only the records with the most
recent transaction date, and I did:

Customer ID | Transaction date | Transaction type
1 | 1/2/2008 | F
1 | 1/2/2008 | R
2 | 1/6/2007 | R

The problem is, each customer can make more than one transaction in
the same day (see above). In these cases, I need to retrieve only one
record per customer, based on the importance of the transaction: F is
more "important" than R which is more "important" than M. So, in the
example above, I would only retrieve transaction F for customer 1 and
disregard transaction R: they were made on the same date, but I am
more interested in F than in R.

Do you have any suggestions on how I could achieve this in SQL?

I use Microsoft SQL server 2005.

Thank you for your help!

Here is one way:

WITH TransCTE
AS
(SELECT customer_id,
transaction_date,
transaction_type,
ROW_NUMBER() OVER(
PARTITION BY customer_id
ORDER BY transaction_date DESC,
CASE transaction_type
WHEN 'F' THEN 1
WHEN 'R' THEN 2
WHEN 'M' THEN 3
ELSE 99 END) AS seq
FROM Transactions)
SELECT customer_id,
transaction_date,
transaction_type
FROM TransCTE
WHERE seq = 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com