Menu
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?
355
The ROW_NUMBER() function uses the OVER and PARTITION BY clause and sorts results in ascending or descending order. It starts ranking rows from 1 per the sorting order.
PARTITION BYIt is an optional clause in the ROW_NUMBER function. It is a clause that divides the result set into partitions (groups of rows). The ROW_NUMBER() method is then applied to each partition, which assigns a separate rank number to each partition.
MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).
Discussion: To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
