Dam Ms Sql Top Option

[morrisjohnny]

Solution

 SELECT * FROM (SELECT row_number() OVER (ORDER BY id ASC) as rownum, * FROM CPLogs /* Any Filtering In Here*/) as a WHERE a.rownum BETWEEN 10 AND 20

I've personally tested

SELECT TOP 10 * FROM CPLogs WHERE id NOT IN (SELECT TOP 20 * id FROM CPLogs ORDER BY id ASC) ORDER BY id ASC

Supplied by JustSomeGuy (Not Tested)Okay coming from MySQL i've become used to using Limit and Offset in order to create a nice pagination However as part of a project I've had to used MS SQL which of course does not have LIMIT so instead i'm using TOP . . . okay trying to use TOPas far as i'm awareThese two statements should create the same effect:

SELECT * FROM table LIMIT 10,20

SELECT * FROM (SELECT 10 * FROM ( SELECT TOP 20 FROM table)as x)as y 

This is my result from running the second statement and moving to a new apge: SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 10 id,userid,timestmp,page FROM CPLogs ORDER BY id asc )as x ORDER BY id asc)as y ORDER BY id ascand the next page shows:SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 20 id,userid,timestmp,page FROM CPLogs ORDER BY id asc )as x ORDER BY id asc)as y ORDER BY id asc however the data that is shows is still the same. After a bit of research i then came across a row_num so i tried this however i could not get this to work either. (could not get the syntax correct) So i'm looking for pointers in the correct direction I'm using these variables incase they are of any use to anyone.$sLimit = (Top Limit) (this is 10 in both above statements)$sLimit2 = (Bottom Limit)$sWhere = is my where statement$sOrder = is my order by statement. Thanks Inadvance JohnnyPS. why can MS SQL not just support LIMIT and OFFSET? although it's a good eye opener.

[justsomeguy]

SELECT TOP 10 * FROM CPLogs WHERE id NOT IN (SELECT TOP 20 * id FROM CPLogs ORDER BY id ASC) ORDER BY id ASC

why can MS SQL not just support LIMIT and OFFSET?

Because that would be too easy.

[jnymris]

Thanks JustSomeGuy (You've always helped on this board and i've been here for a 'while' you seem to be the most helpful person on this board that sticks around so Thank you for that aswell !I did managed to use this (the row_number function) just wondering which of the two would be best for performance/speed?

SELECT * FROM (SELECT row_number() OVER ($sOrder) as rownum, * FROM $sTable $sWhere) as a WHERE a.rownum BETWEEN 10 AND 20 );

[justsomeguy]

My immediate response is that using row_number is slower because it calls a function, but you would have to benchmark those if you want to be sure.