how to get latest record from a table?

hi guys.
i have a table that store all transactions. In this table, a member can has multiple records, there is a field to store the date.
1. the problem i'm facing is, i need to retrieve only the latest record. for example, the transaction table contains of 20 records for 5 members, i need to retrieve the latest record for 5 members.
2. another problem that i face is, how can i make comparison with the date. let say i want to retrieve the record that more than 90 days?

thanks for all advises.

Engloon:

For #1 you need to make sure that you have an index on "member id" so that you can efficiently retrieve records based on the "member id."

For #2 you need to have an index baded on the date and you need to make sure that your field is has a "datetime" datatype.

declare @.member table
( memberId integer,
tranDT datetime
)
insert into @.member
select 5, '1/9/6' union all
select 5, '5/8/6' union all
select 5, '12/15/6' union all
select 5, '3/12/7'
--select * from @.member

/*
memberId tranDT
-- --
5 2006-01-09 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-12-15 00:00:00.000
5 2006-03-12 00:00:00.000
*/

select top 1
memberId,
tranDt
from @.member
order by tranDt desc

/*
memberId tranDt
-- --
5 2006-12-15 00:00:00.000
*/

select memberId,
tranDt
from @.member
where tranDt < getdate() - 90
order by tranDt desc

/*
memberId tranDt
--
5 2006-12-15 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-01-09 00:00:00.000
*/

|||

1. This should give you an idea about how to get the latest entry for each member. (I assume you have a MemberID column.)

Code Snippet

SELECT
MemberID,
max( TransactionDate )
FROM MyTable
GROUP BY MemberID
ORDER BY MemberID

2. Add a WHERE clause, something like this:

Code Snippet

WHERE TransactionDate < ( dateadd( day, -90, getdate() ))

|||many thanks to Arnie Rowland and Kent Waldrop. I appreciate your help.
but the @.member table contains not only 1 member. I'm thinking to use distinct function to get only 1 record for each member.
Will let you know if I can solve the problem.

|||here is the result of my sql query
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
8 18-Sep-2005
8 18-Sep-2005
187 16-May-2006
187 14-May-2006
187 06-Jun-2006
187 29-Jul-2005
187 05-Jan-2007
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
303 19-Dec-2006
306 03-Oct-2005
306 16-Dec-2005

the result that i desire would be like this
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
187 16-May-2006
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
306 03-Oct-2005
|||

Use the following query...

Code Snippet

Select Identity(Int,1,1) as UID,Id,Date Into #Temp from Members;

Select Mem.Id,Mem.Date From #Temp Mem
Join (Select Min(UId) UID,Id from #Temp Group By Id) as LastRec On LastRec.UID = Mem.UID;

Drop Table #Temp;

|||

Strange, it 'sounds' like you are 'blowing off' the solution that I provided. Too bad, because using the data you posted, and the query I provided, the exact resultset you asked for is produced.

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
TransactionDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005' )

SELECT
MembershipID,
TransactionDate = max( TransactionDate )
FROM @.MyTable
GROUP BY MembershipID
ORDER BY MembershipID

MembershipID TransactionDate
3 2007-01-03 00:00:00.000
8 2006-08-05 00:00:00.000
187 2007-01-05 00:00:00.000
195 2006-03-14 00:00:00.000
239 2005-08-29 00:00:00.000
275 2005-08-07 00:00:00.000
303 2006-12-19 00:00:00.000
306 2005-12-16 00:00:00.000

|||

But your query doesn't give the last row, it gives the max tran date. He wants the row with the max tran date:

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
Date datetime,
someOtherColumn int default (100),
yetAnotherColumn int default (100)
)

SET NOCOUNT ON

INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '16-Dec-2005' )

select membershipId, date, someOtherColumn, yetAnotherColumn
from (select membershipId, date, someOtherColumn, yetAnotherColumn,
row_number() over (partition by membershipId order by date desc) as rowNum
from @.MyTable) as myTable
where rowNum = 1

|||

You are right Louis -but in defense, I was working with the data he provided, assuming that we were working with the 'first step'. (It's so much easier when folks provide us with an adequate explanition and sample data...)

The query that I provided should be used as a derived table and then would provide the requested data -and it works in both SQL 2000 and SQL 2005.

(However, there is one significant issue -how to deal with duplicate MembershipID/Date records -and of course, your proposed solution has that same problem).

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
[Date] datetime,
MyOtherColumn int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007', 1 )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006', 2 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 3 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 4 )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006', 5 )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006', 6 )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006', 7 )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005', 8 )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007', 9 )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006', 10 )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005', 11 )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005', 12 )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005', 13 )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006', 14 )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005', 15 )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005', 17 )

SELECT
t.MembershipID,
t.[Date],
t.MyOtherColumn
FROM @.MyTable t
JOIN ( SELECT
MembershipID,
[Date] = max( [Date] )
FROM @.MyTable
GROUP BY MembershipID
) dt
ON ( t.MembershipID = dt.MembershipID
AND t.[Date] = dt.[Date]
)
ORDER BY t.MembershipID

MembershipID Date MyOtherColumn
-
3 2007-01-03 00:00:00.000 1
8 2006-08-05 00:00:00.000 2
187 2007-01-05 00:00:00.000 9
195 2006-03-14 00:00:00.000 10
239 2005-08-29 00:00:00.000 11
275 2005-08-07 00:00:00.000 12
303 2006-12-19 00:00:00.000 14
306 2005-12-16 00:00:00.000 17

|||i'm so sorry, i should give an example of the result i need in the begining.
and many thanks to Arnie and all programmers who tried to help me.
i really appreciate it.