Help using OVER(PARTITION BY ..) feature

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 2012 Forums

Transact-SQL (2012)

Help using OVER(PARTITION BY ..) feature

Author

Topic

Analyzer
Posting Yak Master

115 Posts

Posted - 2014-03-25 : 06:39:29

Hi,

Trying to return results from table also including inner join column on end from 2nd table. Problem is other table column is an INT and when I join on col 'svr_name' the INT value is represented multiple times and I just need the 1st row value.

Using the t-sql below what do I need to change to return the top row of [Value1] when tables are joined on svr_name? Script does not parse, so suspect something is out of place or missing

SELECT t1.Col1, t1.Col2, t1.Col3, t2.Col4,
FROM tbl1 t1
INNER JOIN
( SELECT ROW_NUMBER() OVER(PARTITION BY [svr_name] ORDER BY [Value1] DESC) AS RowNumber
FROM tbl2
) t2
ON t1.ServerName = t2.RowNumber

Thanks in advance

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-03-25 : 07:38:11

You need to include your row_number in a cte, you can then return the row you want where RN = 1

With RNCTE
as
(
SELECT t1.Col1, t1.Col2, t1.Col3, t2.Col4
FROM tbl1 t1
INNER JOIN
( SELECT ROW_NUMBER() OVER(PARTITION BY [svr_name] ORDER BY [Value1] DESC) AS RN
FROM tbl2
) t2
ON t1.ServerName = t2.RowNumber
)
Select t1.Col1, t1.Col2, t1.Col3, t2.Col4
From RNCTE
Where RN = 1

If you will it you can achieve it!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources