The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MS SQL Development
|
SQL Query help
Discuss SQL Query help in the MS SQL Development forum on Dev Shed. SQL Query help MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

September 19th, 2003, 08:02 AM
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 12
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
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
|

September 19th, 2003, 03:19 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
> 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/
|

September 19th, 2003, 04:06 PM
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 12
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
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!
|

September 19th, 2003, 05:10 PM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
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
|

September 19th, 2003, 11:19 PM
|
|
Registered User
|
|
Join Date: Sep 2003
Posts: 12
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
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
Last edited by MKSolutions : September 20th, 2003 at 12:04 AM.
|

September 20th, 2003, 06:14 AM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
|
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
Last edited by r937 : September 20th, 2003 at 06:21 AM.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|