can someone help with this t sql query ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

can someone help with this t sql query ??

Author

Topic

CKHong
Starting Member

13 Posts

Posted - 2010-03-25 : 06:11:00

Name RowNumber
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
C 1
C 2
C 3
C 4
C 5

Above is my data.. how can i query the last 2 row of my RowNumber
Please help..
the result i would like is :

Name RowNumber
A 4
A 5
B 3
B 4
C 4
C 5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 06:14:44

[code]
select *
from
(
select *, row_no = row_number() over(partition by Name order by RowNumber desc)
from yourtable
) t
where t.row_no <= 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 06:16:54

The first way


select name,rownumber from
(
select name,rownumber,seq=row_number()
over(partition by name order by rownumber desc) from tbl
)t where t.seq<=2

the second way


select c.name,c.rownumber from tbl as t
cross apply(select top 2 name ,rownumber from tbl order by rownumber desc where c.rownumber=tbl.rownumber)as c

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 06:19:37

Some other methods
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

CKHong
Starting Member

13 Posts

Posted - 2010-03-25 : 06:24:41

Thank you all for the fast reply.. i'll try it and get back to you all

CKHong
Starting Member

13 Posts

Posted - 2010-03-25 : 06:36:25

Thanks ALOT guys !! the code looks great ~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 06:54:32

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources