SQL Query help

I am trying to convert a MySQL Query to MSSQL. The MySQL query looked like this:

SELECT * FROM TABLE LIMIT 10,25

This query selects the first 10 rows starting with row #30. How is it possible to do this in MSSQL? THe purpose behind it is for a multiple page search result....say you want 10 results per page.

Thanks for the help!
Matthew

> This query selects the first 10 rows starting with row #30
no, it doesn't, it starts at record 11 and selects 25 records from there (in mysql the offset of the initial row is 0, not 1)

to do paging properly, you need either to do it in your scripting language (php, asp, coldfusion...) or else if you try to do it in the sql language, specifically in microsoft sql server, you would ordinarily use the TOP keyword, an ORDER BY clause, and acolumn value parameter

e.g.

select TOP 10 id, name, address
from people
where name > 'jones'
order by name

the column value parameter ('jones' in this example) is what is passed in from the scripting language as the "next" link from the page that showed the last set of names

make sense?

rudy
http://r937.com/

Sorry...I posted what it returned incorrectly.

What I was trying to get at...the TOP command does not include a "start at row" parameter. It allows you to only return the top x rows.

I am still a little confused on how to return the top x rows starting at row y.

Thanks!

select top x+y-1 foo, bar
from yourtable
where foo not in
( select top y-1 foo
from yourtable
order by foo )
order by foo

substitute your numbers for x and y and your column names for foo and bar

foo is the column that determines the sequence that TOP will operate on

rudy


order by foo desc

I got the general idea now. I just think I have the code written wrong here now. What do you think of this?

$x = $start + $display_number;
$query3 = "SELECT TOP $x * FROM photos WHERE category = '$category' AND id NOT IN (SELECT TOP $start id FROM photos WHERE category = '$category' ORDER BY id) ORDER BY id DESC";


It works just on the first page....pages after that contain all the data from the first page plus the next page. So page 10 contains all data from pages 1-10.


I visited your site...and may be contacting you soon about some projects I have coming up.

Thanks for the help so far!

Matthew

well, like i said in my first reply, i wouldn't do it with the double-top (it's not all that efficient)

on the first page, you just get

SELECT TOP $start * FROM photos WHERE category = '$category' ORDER BY id

as you prepare the page, you use the id of the last photo, and use that in the link for the next page

<a href="listphotos.php?howmany=$display_number&startat=$lastid">next $display_number</a>

then the incoming logic pulls $lastid out of the url string, and uses it in the query

SELECT TOP $start * FROM photos WHERE category = '$category'
AND id > $lastid ORDER BY id

rudy