[freetds] dbnextrow() vs dbskiprow() (skipping rows in FreeTDS)

Jason Young (Morgon) morgon at mygamercard.net
Mon Jul 28 13:21:33 EDT 2008


Re-reading these posts, there's really little reason to not use ROW_NUMBER:

    SELECT *
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY author [ASC | DESC]) as Row,
        author, title, description
        FROM books
        GROUP BY author)
    AS Results WHERE Row BETWEEN 51 AND 75

I know it may not seem intuitive for 'generic' queries (as the original 
poster wanted), but there's only 'x' number of ways you'd want to sort 
things ('x' = number of fields in the table, eh?).
Simply set a 'default' method of search (id?), and change the ORDER BY 
clause if the user wants to sort by something else.

I know it's been easy to rely on MySQL to generically sort things for 
you, but it's doing it based off of *something* that you can easily 
re-create.

More info on what Navdeep meant by 'generic' might be more helpful.

David Barnwell wrote:
> You can page through MSSQL tables 20 rows at a time using this SQL:
>
>     SELECT top 20 author, title, description FROM mytable
>     WHERE table_id NOT IN (
>        select top 12345 table_id from mytable
>        order by title
>     )
>     ORDER BY title
>
> Here, table_id = primary key of the table
> 12345 = the next row to return (counting from 0)
>
> I wrote a data browser that allows the user to page through any table 
> and see the first/next/last 20 rows. It's surprisingly fast - on a table 
> with 344000 rows it can select the last 20 rows in about a second.
>
> -- David
>
> Navdeep Shergill wrote:
>   
>> I am trying to implement a mysql like paging; and this is one approach that
>> I am looking at. Mysql makes it realy easy with the LIMIT keyword; but I am
>> not having such luck with MSSQL. I am currently looking at the ROW_NUMBER
>> function ; but even that one requires me to do a order by on some column.
>>
>> The problem is that the user may be trying to run some very generic queries;
>> and I need to be able to provide paging.. so I was looking at some way to
>> exec a query; and then go right to some arbitary row in the result set.
>>     
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>   


More information about the FreeTDS mailing list