Max Values query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max Values query

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2006-09-08 : 14:32:40

I've done this before, but I think I'm having a brain stall. If I have a table with the following columns/data, how do I query it in such a way that returns only the row for each resident with the maximum value for Daterecd, i.e. the most recent payment? Jeez, this is so basic. I must be having a nervous breakdown.

PymtID ResidentsID Last First DateRecd Days Amount
1 1000 Jones Bridget 7/15/2004 7 $777.00
2 1000 Jones Bridget 8/15/2004 31 $6,441.06
4 1000 Jones Bridget 9/15/2004 31 $6,441.06
5 1001 Longstocking Pippi 9/15/2006 39 $7,851.00
6 1001 Longstocking Pippi 10/15/2006 30 $6,441.00
7 1002 Organa Leia 6/15/2006 30 $6,441.00
9 1002 Organa Leia 7/15/2006 30 $6,441.00
10 1002 Organa Leia 8/15/2006 30 $6,441.00

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-09-08 : 14:33:49

Try grouping on the resident id and get the max daterecd value.

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 14:44:43

What do you want to do with ties? Or do you just want the max value?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 14:49:32

Oh, You just want the last payment made


USE Northwind
GO

CREATE TABLE myTable99(
	  PymtID int
	, ResidentsID int
	, [Last] char(20)
	, [First] char(15)
	, DateRecd datetime
	, Days int
	, Amount money)
GO

INSERT INTO myTable99(PymtID, ResidentsID, [Last], [First], DateRecd, Days, Amount)
SELECT 1,  1000, 'Jones'	, 'Bridget', '7/15/2004' , 7 ,  777.00 UNION ALL
SELECT 2,  1000, 'Jones'	, 'Bridget', '8/15/2004' , 31, 6441.06 UNION ALL
SELECT 4,  1000, 'Jones'	, 'Bridget', '9/15/2004' , 31, 6441.06 UNION ALL
SELECT 5,  1001, 'Longstocking' , 'Pippi'  , '9/15/2006' , 39, 7851.00 UNION ALL
SELECT 6,  1001, 'Longstocking' , 'Pippi'  , '10/15/2006', 30, 6441.00 UNION ALL
SELECT 7,  1002, 'Organa'	, 'Leia'   , '6/15/2006' , 30, 6441.00 UNION ALL
SELECT 9,  1002, 'Organa'	, 'Leia'   , '7/15/2006' , 30, 6441.00 UNION ALL
SELECT 10, 1002, 'Organa'	, 'Leia'   , '8/15/2006' , 30, 6441.00
GO

SELECT * 
  FROM myTable99 o
 WHERE EXISTS ( 
		  SELECT i.ResidentsID, MAX(i.DateRecd) AS MAX_DateRecd
  		    FROM myTable99 i
		   WHERE i.ResidentsID = o.ResidentsID
		GROUP BY ResidentsID
		  HAVING o.DateRecd = MAX(i.DateRecd))
GO

DROP TABLE myTable99
GO