Mssql

[stevelibby]

Wonder if anyone can help here, i have created a query that dislays results in a particular order, if i wanted to query something in that list as to which position it is in that query, how would i do this.

[coopster]

You mean which row number it is in the result set?

[stevelibby]

yes thats it, so if a customer said what number in the que am i, how would i construct this in sql? aslo is there a way that i can select certain results in sql, so if i wanted to show results from 20-40, how would i do this, i know that in mysql you can use limit, but what about sql, is there something similar?

[redzone]

Steve,

I've found it's easier to just use a loop with a counter incremented as you "MoveNext" through the resultset.. Physical row numbers don't exist in MSSQL and there is no limit function.

As for your 2nd question, I wrote a specific SQL Query for paging through resultsets for CGI displays...

The following example displays 50 results per page, and I use a Page_Number variable that is incremented/decremented based on a user clicking "Next Page" or "Previous Page" links, or a GoTo Page Number Dropdown in the CGI.

SELECT * FROM (SELECT TOP 50 * FROM (SELECT TOP (50 * Page_Number) * FROM tablename ORDER BY ######) y ORDER BY ###### DESC) x

##### = the column(s) you want to order by.

You have to think a bit out of the box here, as the resultset is in "reverse" order... :)

After you execute this query, you execute a "MoveLast" on the result set.

Then set up a loop to:

Display whatever based on the resultset then
execute a MovePrevious on the resultset

I've found this routine is very efficient working with large resultsets that contain thousands of rows. In a paging environment in a CGI, if you have to move forward through the resultset to get to the correct row, to begin displaying the 150th page, etc.. It is a huge waste of processor and bandwidth...

Let me know if you want to try this approach, and need some additional help...