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
 Latest Record for each Ref field

Author  Topic 

tim8w
Starting Member

10 Posts

Posted - 2014-02-19 : 13:53:55
I have a table that contains a Ref field and a TransactionDate field. For each Ref field there are mutliple Transactions. I am trying to put together a query that selects the Latest Transaction date for each Ref field in the table.

I tried:

SELECT *
FROM tabMediaTransaction
WHERE (TransactionDate =
(SELECT MAX(TransactionDate) AS Expr1
FROM tabMediaTransaction AS tabMediaTransaction_1))


But this only returns 1 record for 1 Ref field. I need 1 record for each Ref field

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-02-19 : 14:28:38
[CODE]select a.*
from (
SELECT *, row_number() over (partition by Ref order by TransactionDate DESC) rn
FROM tabMediaTransaction
) a
where a.rn = 1[/CODE]

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)
Go to Top of Page

tim8w
Starting Member

10 Posts

Posted - 2014-02-19 : 15:02:07
Function row_number() is not recognized...
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-19 : 15:15:07
MS sql server, right? which version are you using?

Be One with the Optimizer
TG
Go to Top of Page

tim8w
Starting Member

10 Posts

Posted - 2014-02-19 : 15:17:17
2008
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-19 : 15:21:15
Please check the database compatibility level.
<right-click database in object explorer> | properties | Options

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-19 : 15:22:16
try this if your compatibility level is below 2005:

SELECT t.*
FROM tabMediaTransaction t
inner join (
select ref, max(transactionDate) maxtdate
from tabMediaTransaction
group by ref
) d
on d.ref = t.ref
and d.maxtdate = t.TransactionDate

This could work too - see which performs better:

select *
FROM tabMediaTransaction t
where exists (
select 1
from tabMediaTransaction
where ref = t.ref
having max(transactiondate) = t.transactionDate
)

Be One with the Optimizer
TG
Go to Top of Page

tim8w
Starting Member

10 Posts

Posted - 2014-02-19 : 17:29:47
That worked... Thanks!
Go to Top of Page
   

- Advertisement -