Question on SELECT statement

Question on SELECT statement

Post by car.. » Thu, 26 Dec 2002 07:43:06



How can I select the 101st to 200th records from a database?  In
MySql, it is SELECT Column FROM Table WHERE Condition LIMIT 101, 200.
I would like to know the MSSQL version of the above syntax.  Please
advise.  Thanks in advance.
 
 
 

Question on SELECT statement

Post by Erland Sommarsko » Thu, 26 Dec 2002 08:08:25


[posted and mailed, please reply in news]


> How can I select the 101st to 200th records from a database?  In
> MySql, it is SELECT Column FROM Table WHERE Condition LIMIT 101, 200.
> I would like to know the MSSQL version of the above syntax.  Please
> advise.  Thanks in advance.

There is no direct syntax, but there are several possibilities. Which
one to pick depends on the problem at hand.

First, consider the possibility to get all rows to the client and do
the paging there. This has the distinct advantage of saving turnaround
time each time you need more data. Obviously, this method takes its
toll in terms of memory, and if the complete result set can be several
thousands of rows, you might still have to do paging. Or just cut when
you get more than, say, 2000 thousand rows. To this end, SQL Server
offers the SET ROWCOUNT command which takes either a constant or a
variable as parameter. When considering this, not only the number of
rows has significance, but also the size of them.

If your data has a single identifying primary key, you can use this for
the first SELECT:

    SELECT TOP 100 col1, col2, .... FROM tbl ORDER BY keycol

Then for the next batches you use:

    SELECT TOP 100 col1, col2, .... FROM tbl

    ORDER BY keycol

clause that I use here, is another way to delimit the size of the
result set. In difference to SET ROWCOUNT, TOP can only take a constant
as parameter.

If your data has a multi-column key, the above method can still be
used, but the WHERE clause becomes messier the more and more key
columns you have.

Another possibility is to use a temp table:

   CREATE TABLE #tmp (rowno int IDENTITY(1, 1) NOT NULL,
                      -- other cols)

   INSERT #tmp(..)  
      SELECT col1, col2 FROM tbl ORDER BY <your criteria>


   ORDER BY rowno

A variation of this theme is to create a permanent table, which you
drop when the paging is no longer in use. The advantage here are
twofold:
1) By running the basic SELECT once, you gain performance for next
   fetches.
2) If new data is inserted while the user is paging, this method will
   not miss rows or include a row twice, because what was row 99 in the
   sort order has become row 101.

Finally, if you are using ADO, I believe there are paging functions
within ADO. Never used them myself, though.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

Question on SELECT statement

Post by Umachandar Jayachandra » Thu, 26 Dec 2002 08:11:36


    Please search in this newsgroup for various solutions. This has been
answered lot of times before. The approach is to use SET ROWCOUNT, COUNT(*)
sub-query filters, or TOP.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

Question on SELECT statement

Post by Kama » Fri, 27 Dec 2002 13:23:47


All the above solutions can be used. One other solution is -CURSORS
Although cursors should be used as last alternative.
Using cursors also it is possible to fetch range of records.

All the best
Kamal Arora

 
 
 

Question on SELECT statement

Post by Stride » Sat, 28 Dec 2002 00:53:28



Quote:> All the above solutions can be used. One other solution is -CURSORS
> Although cursors should be used as last alternative.
> Using cursors also it is possible to fetch range of records.

Two comments:

1) Cursors will be MUCH slower than a set solution.

2) As Joe Celko and possibly others would point out, if you're trying to get
rows 100-201 or something like that, question what you are doing.  In theory
a SQL table is an unordered set of records.  Key word being unordered.
Imposing an "unnatural" order shows a "non-set" type thinking.

Quote:> All the best
> Kamal Arora

 
 
 

1. A question on SELECT statement

Hello!

SQL2K

If I have a table called MyTable with two columns
defined as CHAR(1).  Let's say I have the following
data in MyTable:

-------------
Col01   Col02
-------------
A        1
B        6
B        7
B        8
-------------

How would I write a SELECT that will return the
data in the following format:

-------------
Col01  Col02
-------------
A      1
B      6,7,8
-------------

Any help I can get is greatly appreciated.

Sydney

2. Job Opening - Chicago

3. Question regarding SELECT statement feasibility.

4. SQL server varchar data type is not readable in VB

5. A question in SELECT statement

6. ejob.com

7. Question re Select Statement

8. Data/information [resource] management/administration newsgroup/list

9. Newbie question about Select statement

10. Very simple question about SELECT statement and dates

11. Question about SELECT statement and locks.

12. Question on Select statement

13. Brain damaging question: recursive SELECT statement