SQL Statement to find the the maximum record

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

SQL Statement to find the the maximum record

Author

Topic

rajasql
Starting Member

1 Post

Posted - 2007-10-12 : 16:32:36

I have a table with member information. Each member has multiple effective dates. I would like to query to find the record for each member that has the maximum effective date. A sample table is below:

MEMBERID EFECTIVEDATE
------------ ------------------
699361401 20070101
699361401 20070501
732612701 20070101
732612701 20070501
575424301 20070101
575424301 20070501
192939801 20070101
192939801 20070501
458645001 20070101
458645001 20070501

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-12 : 16:48:47

[code]DECLARE @Member TABLE(MemberID INT, EffectiveDate DATETIME)

INSERT @Member
SELECT 699361401, '20070101'
UNION ALL SELECT 699361401, '20070501'
UNION ALL SELECT 732612701, '20070101'
UNION ALL SELECT 732612701, '20070502'
UNION ALL SELECT 575424301, '20070101'
UNION ALL SELECT 575424301, '20070503'
UNION ALL SELECT 192939801, '20070101'
UNION ALL SELECT 192939801, '20070504'
UNION ALL SELECT 458645001, '20070101'
UNION ALL SELECT 458645001, '20070505'

SELECT
MemberID,
MAX(EffectiveDate)
FROM
@Member AS Member
GROUP BY
MemberID

-- OR

SELECT
MemberID,
EffectiveDate
FROM
(
SELECT
MemberID,
EffectiveDate,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY EffectiveDate DESC) AS RowNumber
FROM
@Member as Member
) AS Temp
WHERE
RowNumber = 1

[/code]

demifelix
Starting Member

22 Posts

Posted - 2007-10-12 : 20:39:03

Hi Lamprey,

I'm not using SQL server 2005 so your code does not work. I am using Microsoft SQL Server Management Studio in SQL Server 2000 environment. Can you please help me do do this same stuff in SQL server 2000? Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-13 : 03:37:40

The 2000 bit is :

CREATE TABLE #temp
(MemberID INT, EffectiveDate DATETIME)
INSERT #temp
SELECT 699361401, '20070101'
UNION ALL SELECT 699361401, '20070501'
UNION ALL SELECT 732612701, '20070101'
UNION ALL SELECT 732612701, '20070502'
UNION ALL SELECT 575424301, '20070101'
UNION ALL SELECT 575424301, '20070503'
UNION ALL SELECT 192939801, '20070101'
UNION ALL SELECT 192939801, '20070504'
UNION ALL SELECT 458645001, '20070101'
UNION ALL SELECT 458645001, '20070505'

SELECT
MemberID,
MAX(EffectiveDate)
FROM
#temp AS Member
GROUP BY
MemberID

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-15 : 03:13:04

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources