March 2, 2016 at 10:14 am
I have a payment table where a customer may have made multiple payments on account. I want to pull the customer id, the amount and the MAX date. Problem is when I ask for MAX date, I have to put the other fields in a GROUP BY to get a return. That gives me all the payments instead of just the one line I want.
CREATE TABLE test_max (id varchar(4), date_created date, amount decimal(8,2))
INSERT INTO test_max
VALUES (1, '2-12-16', '14.00')
, (1,'2-15-16', '35.00')
, (1,'2-28-16', '11.00')
, (1,'2-13-16', '40.00')
SELECT id
, MAX(date_created)
, amount
FROM test_max
GROUP BY id
, amount
What I want to see is just the third line 1, 2-28-16, 11.00 because that is the MAX date with the id and the amount paid on that date.
March 2, 2016 at 10:21 am
This:
with basedata as (
select
rn = row_number() over (partition by id order by date_created desc),
id,
date_created,
amount
from
test_max
)
select
id,
date_created,
amount
from
basedata
where
rn = 1;
March 2, 2016 at 10:21 am
maybe like this?
SELECT *
FROM test_max m
INNER JOIN
(SELECT id
, MAX(date_created) AS LastDate
FROM test_max
GROUP BY id) lst
ON m.id=lst.id
AND m.date_created = lst.LastDate;
You may want to add your fields explicitly to your SELECT statement, as they're probably not all relevant to the answer.
March 2, 2016 at 11:58 am
On SQL Server 2014 I would think this is a great use case for LAST_VALUE().
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 2, 2016 at 1:17 pm
TheSQLGuru (3/2/2016)
On SQL Server 2014 I would think this is a great use case for LAST_VALUE().
I don't think so since the OP only wants to see one row for each id.
March 2, 2016 at 1:29 pm
Lynn Pettis (3/2/2016)
TheSQLGuru (3/2/2016)
On SQL Server 2014 I would think this is a great use case for LAST_VALUE().I don't think so since the OP only wants to see one row for each id.
Ahh, yes. Missed that this was not the typical post I see like this. Thanks.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 2, 2016 at 4:59 pm
Thank you's to both Lynn Pettis and pietlinden. I had tried the nested query but obviously didn't have it correct. I hadn't even thought about the row_number. Both methods work perfectly. Thanks again to both.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply