September 19th, 2003, 09:02 AM
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!
September 19th, 2003, 04:19 PM
> 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
select TOP 10 id, name, address
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
September 19th, 2003, 05:06 PM
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.
September 19th, 2003, 06:10 PM
select top x+y-1 foo, bar
where foo not in
( select top y-1 foo
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
order by foo desc
September 20th, 2003, 12:19 AM
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!
Last edited by MKSolutions; September 20th, 2003 at 01:04 AM.
September 20th, 2003, 07:14 AM
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
Last edited by r937; September 20th, 2003 at 07:21 AM.