Getting a fixed number of records through select sql

Databases
1

Hi
Here’s my problem. I’m querying a table having somewhere about 15000-20000 records. I just want the 5-10 records based on date in descending order, no more than that.
Now usually what I did was select the records & then display only the required number of them, but in this case its gonna crash the script, the number of records being decently high.:wink:

So, I know that I’ll have to sort the records like


select * from myTable orderby date, desc

Now, how should I specify the number of records I want fetched, since the date is not in sequence.
Say, I want 5 records, how do I tell the query engine that all I want is 5 records, don’t fetch more than that?

It should be something simple, just can’t get my mind to work at the moment. By the way, I’m using MS-SQL Server 2000, but give me a solution only in SQL query, since I’m barely literate in MS-SQL Server.:smiley:

Hope to get an answer soon, or I’ll be history …:wink:

2

blah blah blah TOP 5
for mySql its: blah blah blah LIMIT 5

( i think )

3

pretty close, phil, except the TOP 5 has to come right after the SELECT

select top 5 * from myTable order by date desc
4

Thanks!!:smiley:

And for mySQL, will it be


select LIMIT 5 * from myTable order by date, desc

??:confused:

5

no, for mysql, LIMIT 5 goes at the end

6

Like this


select * from myTable order by desc LIMIT 5

??:confused:

7

well, yes, as far as the LIMIT placement is concerned, but you forgot the column to sort by!

8

oh!!:eek:
my typo!!:blush:

But thanks for the help. You really saved my day.:wink:
I asked for 1 solution & I got 2, what more can one ask??:smiley:

9

well, yeah, but you can only use one or the other of those solutions, but not both

10

that’s what I mean.

TOP n for MS databases like Access & SQL-Server
LIMIT n for mySQL.

Thanks again.:smiley:

11

I need to divide the data coming back from a table into pages (using ASP and SQL Server). I know if I was using MySQL I could use LIMIT like so:-

(I know this is php, please ignore that - its the SQL Server bit I’m stuck on!!)


 $perpage=20;
 
 if(isset($_SERVER['PHP_SELF'])) {
 	$thisp=$_SERVER['PHP_SELF'];
 }
 
 if(isset($_GET['pageno'])) {
 	$pageno=$_GET['pageno'];
 } else {
 	if(isset($_POST['pageno'])) {
 		$pageno=$_POST['pageno'];
 	} else {
 		$pageno = "";
 	}
 }
 if($pageno=="") { $pageno=1; }
 $limitfrom=(($pageno-1)*$perpage)+1;
 $sqllimitfrom=$limitfrom-1;
 $limitto=$limitfrom+($perpage-1);
 
 $sqllimit = "limit " . $sqllimitfrom. ", " . $perpage
 $sql = "select blah from blah " . $sqllimit

How do I use TOP to select page ranges??
I can use “select TOP 20 blah from blah” but what about for the second page, how do I specify that I want records 21 - 40?

Many thanks!

Laurie

12

to use TOP, see this thread

13

Okay - thanks for the pointer! I’m getting there on that side of things - however another query has come up.

To work out whether to include a next page link, I need to count the number of results for the sql query but without the TOP qualifier.

I don’t seem to be able to use objRS.RecordCount to get the number of results, (a) it doesn’t seem to work even though I specified adOpenKeySet on the Open, and (b) because I am using TOP in the select statement, even if I could make it work, I’m sure it would return the number I use in the TOP statement. I need to get the total results for my query without the TOP.

So my script now makes a modified SQL statement (as well as the original) which I have made something like:-


 
 SELECT COUNT(*) FROM Company WHERE Company_ID = '5000000'

and I have some ASP ready to use this SQL, but because its not actually a ‘results set’ that I’m getting back, rather it’s just a single value, with no field name, how do I look up that value?


 
 	Dim objCountRS
 	Set objCountRS = Server.CreateObject("ADODB.Recordset")
 	objCountRS.Open strSQLCount, objConn
 	If Not objCountRS.EOF Then
 		How do I say get the count here?
 	        count = objCountRS("COUNT") ???
 	End If
 	objCountRS.Close
 	Set objCountRS = Nothing

Many thanks for your help so far!

14

give it a column alias in the query:

select count(*) as thecount
from …

15

Yeah.
And if you write your sql query like this,


select Count(*) as Num from myTable

Then you can retrieve the number of recorsets returned as


count = objCountRS.Fields("Num")

So the variable “count” will hold the number of recordsets returned.

Another way of dividing records in many pages is:-


select TOP 10 * from myTable order by date desc

The above query is for 1st page. Then in 2nd page, you need to place this query


select TOP 10 * from (select TOP 20 * from myTable order by date desc) order by date

Here in the 2nd query, what you are doing is getting 20 records & you have to sort out 10 records which you have used. Since we have sorted in descending order, now in the main query we sort in ascending order to get the unused records.
So the next query for the third page will be


select TOP 10 * from (select TOP 30 * from myTable order by date desc) order by date

You just have to increase the number of the records fetched in the sub-query(the inner query).

But be warned that this technique is a resource hog & will slow down everything as you reach the last pages if the table contains quite a lot of records. But it works nicely with tables that has upto 1000-2000 records.
The better technique is the one mentioned in the thread which Rudy pointed, but this technique can be used where you don’t have an ID column or the values in the ID column are not consequetive.