|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
May 28th, 2004, 10:29 AM
|
|||
|
|||
|
MS SQL Limit number of records returned (n00b)
Hello all! I am a new green web designer fresh out of college. I didn't have the luxury of being taught MS SQL in school, but I did learn mySQL.
For my current project I am converting an old website to MS SQL from Access. Despite the weird changes that had to be done to the code to get things to work, it's gone smoothly otherwise. The problem is that on this one query that I create, I need to limit the range of rows that come out. I've looked the internet over high and low, even paying some sites that claimed to have the answer (lesson learned). What I'm trying to do is this QUERY: SELECT *,navigation.pageid AS nPageid FROM [pagetext] INNER JOIN [navigation] ON pagetext.[index] = navigation.pageid WHERE pagetext.[index] = navigation.pageid AND pagetext.bDelete = 0 ORDER BY document LIMIT 20,30 or more sepcifaiclly "SELECT *,navigation.pageid AS nPageid FROM [pagetext] INNER JOIN [navigation] ON pagetext.[index] = navigation.pageid WHERE pagetext.[index] = navigation.pageid AND pagetext.bDelete = 0 ORDER BY document LIMIT " & var1 & " , " & var2 & " ;" I've searched the net, and the closest thing I could find that could do this is SELECT TOP 20 That doesn't have a range you can select (20-30)
|
|
#2
May 28th, 2004, 02:19 PM
|
|||
|
|||
|
Basically there is no equivalent to the 2-argument LIMIT in MS SQL Server, at least not that I'm aware of.
__________________
====== Doug G ====== I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain
|
|
#3
May 28th, 2004, 02:25 PM
|
||||
|
||||
|
It appears you may be trying to create some sort of record paging on your site?
SQL Server doesn't support a select range type of query. If you are trying to create paging, there are several examples on this forum of how to go about doing it.
|
|
#4
May 31st, 2004, 09:38 AM
|
|||
|
|||
|
Yes, I'm currently using the Recordset fucntions to controll what gets outputted. But the problem remains about re-inserting things into the page. The Limit function in mySQL would have worked as a perfect solution. I Guess I'll either write an angry letter to microsoft threathening to buy their company via hostile take over..... or find another way of doing what I've been working.
|
|
#6
July 20th, 2004, 03:05 PM
|
|||
|
|||
|
I needed the same question answered. I read this forum first and saw there was no answer. After I found the answer, I thought I would post it here. Here it is...
select top 20 * from table where pkc not in (select top 10 pkc from t order by pkc) order by pkc
|
|
#7
September 30th, 2004, 10:45 AM
|
|||
|
|||
|
For Java Developers - No ? parameter to top
Quote:
Thanks for the tip. One problem I found with this method is that it doesn't support parameterized values from within Java. So, this: Code:
int size = 10;
PreparedStatement stmt = connection.prepareStatement
("SELECT top ? * FROM users WHERE lastname=?");
stmt.setInt (1, size);
stmt.setString (2, "McNealy");
won't work. You'll have to use string concatentation instead. Like this: Code:
int size = 10;
PreparedStatement stmt = connection.prepareStatement
("SELECT top "+size+" * FROM users WHERE lastname=?");
stmt.setString (1, "McNealy");
-Dae
|
|
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > MS SQL Limit number of records returned (n00b) |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
