troubleshooting Question

How to do equivalent my sql limit in mssql 2008

Avatar of Bastyon
Bastyon asked on
Microsoft SQL Server 2008
5 Comments1 Solution249 ViewsLast Modified:
I've been looking online and found something like this

Select v.comp_system_id, v.comp_system, v.rnum
from (Select distinct dbo.vw_cs_comp_sys.comp_system_id, dbo.vw_cs_comp_sys.comp_system, dbo.vw_cs_comp_sys.comp_sys_state,
rnum = ROW_NUMBER() Over (Order By vw_cs_comp_sys.comp_system_id)
from dbo.vw_cs_comp_sys) as v Where v.comp_sys_state = 'somevalue'

but the problem with this is that it sends back the numbers of the rows as they exist in the entire result set not just the number returned

notice how the rnum column skips numbers

comp_system_id      comp_system                         rnum
2                             cjb_test_system1                       1
4                             CJB_Test_4                                3
5                             cjb_test_system4a                       4
6                            cjb_test_system5ab                       5
7                            cjb_test_system6                       6

Now if I do it this way...

  Select distinct dbo.vw_cs_comp_sys.comp_system_id, dbo.vw_cs_comp_sys.comp_system,
rnum = ROW_NUMBER() Over (Order By vw_cs_comp_sys.comp_system_id)
from dbo.vw_cs_comp_sys Where vw_cs_comp_sys.comp_sys_state = 'SomeValue'

I get this back

comp_system_id      comp_system             rnum
2                            cjb_test_system1      1
4                            CJB_Test_4                      2
5                            cjb_test_system4a      3
6                            cjb_test_system5ab      4
7                             cjb_test_system6      5

now that looks like what I want   the rows numbered by their returned row value...the problem with the second version is I cant get it to limit results..

If I add  And rnum between 1 and 10 I get a syntax error on the rnum.

SO I guwsss what IM tryign to figure out is how I can get the same results Meaning whatever the resuterned set if based on its where clause criteria  I can then limit the numebr of rows returned...and specify it in different value  (like perhaps I want rnum bwteen 11 and 20  or 21 and 30  etc etc for paging
Ephraim Wangoya
Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros