Most recent data only - 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

Most recent data only

Author

Topic

KG07
Starting Member

8 Posts

Posted - 2010-02-03 : 09:23:15

I have a database of people and a database of their "transactions" I want to create a query which shows me every person, and only their most recent transaction. Is their a command I can put in the select line which will produce this result?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 09:25:23

By database did you mean tables?

Madhivanan

Failing to plan is Planning to fail

KG07
Starting Member

8 Posts

Posted - 2010-02-03 : 09:29:10

Yes...sorry

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 09:44:19

Post the structure of the tables

Madhivanan

Failing to plan is Planning to fail

KG07
Starting Member

8 Posts

Posted - 2010-02-03 : 09:55:24

I don't know exactly what that means, so hopefully this helps.
dbo.tblpeople inlcudes fields: name, idnumber, address, etc
dbo.tbltransactions includes fields: idnumber, transactiontype, transactionitem, transactionamount, transactiondate

They are inner joined on id number, and each person has multiple transactions. I need to see every person, but with only their most recent transaction.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 10:05:25

Try

select p.name,t.* from dbo.tblpeople as p inner join
(
select * from dbo.tbltransactions as t where transactiondate=(select max(transactiondate) from dbo.tbltransactions where idnumber=t.idnumber)
) as t
on p.idnumber=t.idnumber

Madhivanan

Failing to plan is Planning to fail

KG07
Starting Member

8 Posts

Posted - 2010-02-03 : 10:17:36

Perfect, thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-04 : 01:32:42