RowID in MSSQL

[Claus Seerup]

Hi all

Say, "SELECT * from Customers" returns 30 rows.

However, I am only interested in row 10-20. How do I get MSSQL to return
only row 10-20 through SQL?

Thanx
Claus

[Andrew J. Kelly]

Claus,

What determines rows 10 -20? SQL Server does not know one row from another
except for the data and will not return it in any specific order unless you
use an ORDER BY clause. So why not use an order by and a TOP 10 to get the
10 rows you want.

--
Andrew J. Kelly
Targitmail.com

"Claus Seerup" <see...@mail.sonofon.dk> wrote in message
news:eDRn3gI3AHA.2160@tkmsftngp02...

[BP Margolin]

Claus,

Tables and virtual tables by definition are unordered sets. So the concept
of a first row, a second row, a tenth row in either a table or a virtual
table is meaningless in SQL (the language). One, ideally, needs to be able
to create a query that returns the information based on the data in the
table.

Try:

use pubs

-- option #1
select *
from (select a1.*,
rowid = (select count(*) from authors as a2
where a2.au_id <= a1.au_id)
from authors as a1) as a
where rowid between 10 and 20
order by rowid

-- option #2
select *,
rowid = IDENTITY (int, 1, 1)
into #a
from authors
order by au_id

select *
from #a
where rowid between 10 and 20
order by rowid

-- option #3
select top 11 *
from (select top 20 *
from authors
order by au_id) as a
order by au_id desc

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Claus Seerup" <see...@mail.sonofon.dk> wrote in message
news:eDRn3gI3AHA.2160@tkmsftngp02...

[Claus Seerup]

Well, nothing determines rows 10-20! On my webpage the user can search in my
MSSQL database. Sometimes the returned recordset i too large to fit in one
screen, so the result in devided into more subpages:

Page 1 2 3 4 5

How do i do that without having MSSQL return all records?

"Andrew J. Kelly" <ake...@targitmail.com> skrev i en meddelelse
news:eAFwHpI3AHA.1344@tkmsftngp04...

[Michael MacGregor]

There's a couple of ways of handling this. Let the application code handle
the paging using components so the data is only retrieved once, or add a
fake sequence number to the result set, such as by placing it first in a
temp table or by other means, then use this sequence number to determine
where the sub-result set returned to the application is to begin or end. In
other words, the application has to know the start and end sequence number
and use those when requesting either the next or previous page.

You will also need to determine paging, if you allow the user to select from
any of the pages, easy calculation based on page number and number of rows
per page.

You can also, using SPs, implement what we called a VCR style navigation,
using First (|<), Previous (<), Next (>) and Last (>|). This one is a little
more difficult than simple paging but can be done.

Michael MacGregor
Database Architect
SalesDriver

[Todd Beaulieu]

come on, guys. it's NOT meaningless. if he wants to page thru rows, he can
certainly do this with the WHERE clause, resuming where the previous select
left off, and then use the TOP n to specify how many rows from that point to
return. he just needs to make sure that he uses the same order for each
select, of course, and that there is a primary key to use to track the first
row returned in each group.

"BP Margolin" <bpm...@attglobal.net> wrote in message
news:#ObYQsI3AHA.1308@tkmsftngp07...

[Michael MacGregor]

If you have a PK that can be used in the ORDER BY, providing that the PK is
suitable for the order in which you want the data, then that is an easy way
to deal with the problem. However, the order in which you want the data to
appear may not be the same as the ordered sequence imposed by ORDER BY <PK>,
in which case you have to use artifice.

What BP is saying is an interpretation of set theory upon which all RDBMS
are built.

[BP Margolin]

Todd,

Ordering within a table IS meaningless. You are confusing a recordset
returned to a client application with a table. They are NOT the same.
Recordsets are files with records and thus have an ordering. Tables, as I
posted, by DEFINITION, do not have an ordering.

You might believe that this is nit-picking ... and I understand that. But it
really isn't. It is exactly equivalent to a user detailing his requirements
to you, but trying to sound knowledgeable and using technical terminology
incorrectly. It is like a user saying "I get a message saying that I need
more memory ... install a larger hard disk." I've known help disk personnel
who would assume that the user knew what he was talking about, and write up
a request for a larger hard disk.

Terminology is important because words, especially on these newsgroups, are
the ONLY medium we have to exchange ideas. If we misuse the words, then the
ideas we have meant to convey are not being communicated properly.

I understand that terminology is sometimes loose, and I posted two queries
that attempted to do what I believed you were requesting. If neither of
those queries satisfy your requirements, then, first, I apologize, but
second you should take it that perhaps, just perhaps, you were not
expressing your thoughts properly.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Todd Beaulieu" <noth...@nowhere.com> wrote in message
news:#f0wbvL3AHA.1936@tkmsftngp04...