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
 Top 1 selection

Author  Topic 

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-12-29 : 06:54:18
Dear All,
I have a transaction table with timestamp for different accounts.

I need to select latest top 2 transaction from each accounts from the same table

Thanks,
Gangadhar

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-29 : 07:04:41
hi

Can you post the table structure..


-------------------------
R...
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-29 : 07:11:18
Refer this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-29 : 07:14:45
If it's sql server 2005 then you can do this

DECLARE @accounts TABLE (
[accountNo] INT
, [timeStamp] DATETIME
, [deposit] MONEY
, [withdrawl] MONEY
)

INSERT @accounts ([accountNo], [timeStamp], [deposit], [withdrawl])
SELECT 1, '20091203', 50, 0
UNION SELECT 1, '20091202', 0, 150
UNION SELECT 1, '20091201', 10000, 0
UNION SELECT 2, '20060101', 500, 0
UNION SELECT 3, '20091109', 0, 40
UNION SELECT 3, '20090101', 100, 0
UNION SELECT 3, '20091201', 0, 1000
UNION SELECT 3, '20050505', 0, 10

SELECT
a.[account No]
, a.[time stamp]
, a.[Deposit]
, a.[Withdrawl]
FROM
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY [accountNo] ORDER BY [timeStamp] DESC) AS [pos]
, [accountNo] AS [Account No]
, [timeStamp] AS [Time Stamp]
, [deposit] AS [Deposit]
, [withdrawl] AS [Withdrawl]
FROM
@accounts
)
a
WHERE
a.[pos] < 3



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
   

- Advertisement -