SELECT latest transactions

Question

SELECT latest transactions

Minnesota - Viking

Hall of Fame

Points: 3469
More actions
January 24, 2011 at 2:47 pm

#230799

I have a table with the following tructure
CREATE TABLE [dbo].[Transactions](
[AccountNbr][varchar](16) NOT NULL,
[TransactionAmount][numeric](13, 2) NOT NULL,
[TransactionType][numeric](4, 0) NOT NULL,
[TransactionDescription] [varchar](25) NOT NULL,
[ProcessingDate][smalldatetime] NOT NULL,
[TransactionDate][smalldatetime] NOT NULL,
) ON [PRIMARY]
I need to select the latest transactions for each account number where transaction type is 4
How do i do that ?
I tried a couple ways but ending up getting duplicates. But i need to slect distinct ..
i also tried the follwoing
SELECT DISTINCT AccountNbr, TransactionDate, TransactionAmount
FROM Transactions o
WHERE TransactionDate = (SELECT MAX(TransactionDate)
FROM Transactions i
WHERE TransactionType = 4 AND
o.AccountNbr = i.AccountNbr )
AND TransactionCategory = 4
ORDER BY AccountNbr
Please help.
Thanks
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

by using the row_number() function,a nd a subquery to limit the results ot the row number, you'll get one(max) record for each account...that's because of the partition by.

try this:

SELECT AccountNbr, TransactionDate, TransactionAmount FROM

(

SELECT

AccountNbr,

TransactionDate,

TransactionAmount,

Row_number() OVER (PARTITION BY AccountNbr, TransactionDate DESC ORDER BY AccountNbr) AS RW

FROM Transactions o

WHERE TransactionCategory = 4

) MyAlias

WHERE RW = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Haven't tested it, but shouldn't the code look like this?

SELECT AccountNbr, TransactionDate, TransactionAmount FROM

(

SELECT

AccountNbr,

TransactionDate,

TransactionAmount,

Row_number() OVER (PARTITION BY AccountNbr ORDER BY TransactionDate DESC ) AS RW

FROM Transactions o

WHERE TransactionCategory = 4

) MyAlias

WHERE RW = 1

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 3

both of them throw syntax errors

First one says "Incorrect syntax near the keyword 'DESC'."

Second one says "Incorrect syntax near the keyword 'DESC'."

I never used partition by so i am not not sure where teh error is

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 4

And also if you can tell me why my code failed to acheive the results that wud be great ..

Thanks

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]