using row_number() over(partition by...)

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

SQL Server 2005 Forums

Transact-SQL (2005)

using row_number() over(partition by...)

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-01-23 : 11:47:40

Hi,

am using row_number() function, i want to display only those records which have [rownum] = 1 ... wrote something like ::

SELECT
a.AccountName,
TopAmount=t.Amount,
row_number() over(partition by AccountName order by AccountName) as [rownum]
FROM
ACCOUNT a
JOIN
[transaction] t
ON
a.AccountId = t.AccountId
WHERE
[rownum] =1
ORDER BY
AccountName

But showing error invalid field [rownum]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-23 : 11:51:36

[code]
select * from
(
SELECT
a.AccountName,
TopAmount=t.Amount,
row_number() over(partition by AccountName order by AccountName) as [rownum]
FROM
ACCOUNT a
JOIN
[transaction] t
ON
a.AccountId = t.AccountId
) as derived_table
WHERE
[rownum] =1
ORDER BY
AccountName
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources