MySQL SORT by Column and LIMIT Rows using classic ASP

[technoid]

I started to add paging code to my project at work and I can't seem to get sorting and paging to work at the same time. I'll put the querries below for them separately but how do I start at a row offset and sort too.

Example: 50 Rows in table
I want to start at row 0, 10, 20, 30, 40 and display 10 rows but also sort them by any column. Here's the two querries I used before I tried to add paging and just tried to add display row offsets. That works but I can't seem to get the right combination to get paging and sorting together. I'm using classic ASP to write the code.

QUERRY TO LIMIT ROWS
Set oRS = oConn.Execute("SELECT * FROM `tracking` LIMIT " & startRecord & "," & endRecord)

QUERRY TO SORT BY ONE OF THE COLUMNS IN THE TABLE
Set oRS = oConn.Execute("SELECT * FROM `tracking` ORDER BY `model`")

Thanks for any help.

[Sycario]

have you tried:
Set oRS = oConn.Execute("SELECT * FROM `tracking` ORDER BY `model` LIMIT " & startRecord & "," & endRecord)

[technoid]

Originally Posted by CaNiBuS

have you tried:
Set oRS = oConn.Execute("SELECT * FROM `tracking` ORDER BY `model` LIMIT " & startRecord & "," & endRecord)

Sorry, I tried that but didn't think to put it in the question. It gave me the correct number of rows but sorted the whole table not just the rows the limit set. I did find a work around since our usage is small by using some ASP code to semi sort it by storing all the row IDs in an array and then going through the whole table and only outputing the correct rows. That gave me a sorted limited page but it wasn't somthing I'd do for anything large.

It turns out the Boss didn't like the limited page sort so we got rid of it. I worked for over a day on it and then when he saw it running he decided he would rather have the whole table sorted like it did by default Oh well, at least they paid me for it

Thanks for your help.

[t3hl33td4rg0n]

Personally I always found it annoying when "search results" were limited without any orthodox means of increasing the limit.

Like, searching for an item on siteX returns 900 results with each page only showing 5 items! wtf! lol...

I always try to include an options for sorting, number of results per page, etc.

[technoid]

Originally Posted by t3hl33td4rg0n

Personally I always found it annoying when "search results" were limited without any orthodox means of increasing the limit.

Like, searching for an item on siteX returns 900 results with each page only showing 5 items! wtf! lol...

I always try to include an options for sorting, number of results per page, etc.

I ended up providing lots of options for page size, 10, 15, 20, 25, 30, 40, 50, All. That way each person should be able to pick a page size that fills their screen. The page size is stored in a persistent cookie so they have the same number of lines per page when they return to the site. You can also sort on any column.