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!

CREATE TABLE #X
(Cust int, TranDate datetime, TranType char(1))

INSERT #X values (1, '1/2/2008', 'F')
INSERT #X values (1, '1/4/2007', 'M')
INSERT #X values (1, '1/2/2008', 'R')
INSERT #X values (2, '1/5/2007', 'M')
INSERT #X values (2, '1/6/2007', 'R')

WITH Ranked AS
(
SELECT *,
rank() over (PARTITION BY Cust
ORDER BY TranDate desc,
CASE TranType
WHEN 'F' THEN 'C'
WHEN 'R' THEN 'B'
WHEN 'M' THEN 'A'
END) as Priority
FROM #X
)
SELECT *
FROM Ranked as A
WHERE A.Priority = 1

Note that I used A, B, C rather than the more logical 1, 2, 3 because
I wanted to emphasize that the column Priority was the number assigned
by Rank, and remove any chance of confusing the CASE epxression with
that column.

Roy Harvey
Beacon Falls, CT

On Fri, 22 Feb 2008 05:49:22 -0800 (PST), "myemail.an@googlemail.com"
<myemail.an@googlemail.com> wrote:

>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

Thank you all for your precious tips!