How to get the max row number per group/partition in SQL Server?

Question

I'm using SQL Server 2005. I have a payments table with payment id's, user id's, and timestamps. I want to find the most recent payment for each user. This is easy to search and find an answer for. What I also want to know though is if the most recent payment is the user's first payment or not.

I have the following which will number each user's payments:

SELECT     p.payment_id,     p.user_id,     ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date) AS paymentNumber FROM     payment p 

I'm not making the mental leap which then lets me then pick the highest paymentNumber per user. If I use the above as a subselect by using MAX(paymentNumber) and then grouping by user_id, I lose the payment_id which I need. But if I also add the payment_id into the group by clause, I'm back to one row per payment. I'm sure I'm overlooking the obvious. Any help?

Answer 1

Try this:

SELECT a.*, CASE WHEN totalPayments>1 THEN 'NO' ELSE 'YES' END IsFirstPayment   FROM(                 SELECT  p.payment_id,                                      p.user_id,                                      ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date DESC) AS paymentNumber,                                 SUM(1) OVER (PARTITION BY p.user_id) AS totalPayments                     FROM payment p              ) a WHERE   paymentNumber = 1