Unable to get the last date from the query

Question

Unable to get the last date from the query

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The tables in your query don't bear any relation to your sample data. And please use a universal format for dates, since I got a character conversion error when I tried to run the inserts. Finally, please show us your expected results, since it's not clear from your description what you're looking for.

Would something like this work?

SELECT
    MAX(TransactionDate)
,   NLNominalAccountID
,   NLNominalTranTypeID
,   AccountNumber
FROM mytable
GROUP BY
,   NLNominalAccountID
,   NLNominalTranTypeID
,   AccountNumber;

John

gazy007 Default port Points: 1491 More actions · Answer 2

Hi John,

I have tried the same solution as yours but it brings every date of transaction against the account.

Michael L John One Orange Chip Points: 27079 More actions · Answer 3

gazy007 wrote:

Hi John,
I have tried the same solution as yours but it brings every date of transaction against the account.

Do you want the last(most recent?) date by account number? Or by some other combination of the columns?

If it is only by account number, then the group by in John's query needs to change.

In your original query, you are joining on the account number column. You would need to join on BOTH the account number and the date.

Are you really still using SQL 2008?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27079 More actions · Answer 4

Also, and I realize that this is test data, but a date column may not make a good primary key. I'm guess that there can be two transactions on the same day.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

gazy007 Default port Points: 1491 More actions · Answer 5

Thanks, John and Michael. It worked after Grouping the account number.