asked on

How to do equivalent my sql limit in mssql 2008

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

Kevin Cross

Hi.

SQL 2012 will introduce syntax equivalent to LIMIT ... OFFSET in MySQL, but you are correct that you can simulate this via ROW_NUMBER(). The key is to use the PARTITION BY in the OVER() analytic clause. If the issue is you have duplicates, you will want to use DENSE_RANK() instead: http://msdn.microsoft.com/en-us/library/ms173825(v=sql.100).aspx

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

On re-reading, I am pretty certain the issue is with the lack of PARTITION BY, which is why the above code suggestion will work as it moves the WHERE filter inside the subquery. If you need to filter outside the subquery, make the OVER() like this.

rnum = ROW_NUMBER() OVER(PARTITION BY vw_cs_comp_sys.comp_sys_state 
                         ORDER BY vw_cs_comp_sys.comp_system_id)

Open in new window

If you can move the outer WHERE condition, see above suggestion.

Dave Baldwin

I have used the code from this page and it worked well for me. http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

PortletPaul

"DISTINCT" with row_number() is an oddity, in fact they are in competition

and row_number() wins.

try this:

;WITH
oddity
      AS (
           SELECT 'a' AS code, 'b' AS grp UNION ALL
           SELECT 'a' AS code, 'b' AS grp UNION ALL
           SELECT 'a' AS code, 'b' AS grp UNION ALL
           SELECT 'a' AS code, 'b' AS grp UNION ALL
           SELECT 'a' AS code, 'b' AS grp UNION ALL
           SELECT 'a' AS code, 'b' AS grp
         )
SELECT DISTINCT
  code
, row_number() over (ORDER BY grp)
FROM oddity

Open in new window

row_number() produces a unique integer for each row (or for each row within a 'partition') hence asking for 'distinct' on top of this really does nothing but add cycles to the query.