FreeTDS Development Group

["Navdeep Shergill" <jatshergill AT gmail.com>]

I ended up using Row_Number.. That worked out fine for me. Thanks for your
help everyone

On Mon, Jul 28, 2008 at 10:21 AM, Jason Young (Morgon) <
morgon AT mygamercard.net> wrote:

> 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
> >
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>