select greatest rownum grouped by field

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)

select greatest rownum grouped by field

Author

Topic

Tyork
Starting Member

6 Posts

Posted - 2011-09-20 : 09:37:59

Need code to group by F1 but grab only the rownums with the greatest value. row number is not an actual field. sample table below and required query results below.

RowNum F1 F2
-- -- --
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 1 7
8 2 1
9 2 2
10 2 5
11 2 4
12 2 3

Code would generate:

RowNum F1 F2
-- -- --
7 1 7
12 2 3

Any help would be appreciated.

Tyork

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 09:45:31

Hello,

Do you have a clustering index? Without one, I cannot see how you can determine the ordering of the data blocks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-20 : 09:53:02

select rownum, f1, f2
from (
select rownum, f1, f2, row_number() over (partition by f1 order by rownum desc) as recid from dbo.table1
) AS d
where recid = 1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources