Select specific number of records

[Martin Feuersteiner]

Dear Group

I wonder whether you can give me a syntax example for a SQL Statement.

Lets assume I've a table containing three columns ContactID (Primary Key),
Firstname and Lastname.

I would like to write a stored procedure which returns me the first ten
records and increments an outside variable each time it runs.
E.g If I run it the first time I pass the variable as 0 and it will return
me the first ten records and returns the variable value 1.
When run a second time, I will pass the variable as 1 and it will return me
records 11-20 and sets the variable to 2 and so on...

The difficult thing is how to tell to return me records 11-20. I can't use
the ContactID as someone might have deleted a row and e.g. ContactID 18 is
missing. In this case I only would get 9 rows returned. It always should be
ten.

Thanks very much for your time and efforts!

Kind Regards,

Martin

"There are 10 types of people in this world: Those that understand binary
arithmetic, and those that don't."

[Aaron Bertrand - MVP]

This is called paging; see some possible techniques here.

http://www.aspfaq.com/2120

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:bu**********@sparta.btinternet.com...

Dear Group

I wonder whether you can give me a syntax example for a SQL Statement.

Lets assume I've a table containing three columns ContactID (Primary Key),
Firstname and Lastname.

I would like to write a stored procedure which returns me the first ten
records and increments an outside variable each time it runs.
E.g If I run it the first time I pass the variable as 0 and it will return
me the first ten records and returns the variable value 1.
When run a second time, I will pass the variable as 1 and it will return me records 11-20 and sets the variable to 2 and so on...

The difficult thing is how to tell to return me records 11-20. I can't use
the ContactID as someone might have deleted a row and e.g. ContactID 18 is
missing. In this case I only would get 9 rows returned. It always should be ten.

Thanks very much for your time and efforts!

Kind Regards,

Martin

"There are 10 types of people in this world: Those that understand binary
arithmetic, and those that don't."

[Jacco Schalkwijk]

See:

http://www.sqlserverfaq.com/content/...wer.aspx?ID=51

And while you are at that site, you can search for more articles about
"paging". Tony Rogerson, who runs the site, has done quite a bit in that
area.

--
Jacco Schalkwijk
SQL Server MVP
"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:bu**********@sparta.btinternet.com...

Dear Group

I wonder whether you can give me a syntax example for a SQL Statement.

Lets assume I've a table containing three columns ContactID (Primary Key),
Firstname and Lastname.

I would like to write a stored procedure which returns me the first ten
records and increments an outside variable each time it runs.
E.g If I run it the first time I pass the variable as 0 and it will return
me the first ten records and returns the variable value 1.
When run a second time, I will pass the variable as 1 and it will return me records 11-20 and sets the variable to 2 and so on...

The difficult thing is how to tell to return me records 11-20. I can't use
the ContactID as someone might have deleted a row and e.g. ContactID 18 is
missing. In this case I only would get 9 rows returned. It always should be ten.

Thanks very much for your time and efforts!

Kind Regards,

Martin

"There are 10 types of people in this world: Those that understand binary
arithmetic, and those that don't."

[John Gilson]

"Martin Feuersteiner" <th************@hotmail.com> wrote in message
news:bu**********@sparta.btinternet.com...

Dear Group

I wonder whether you can give me a syntax example for a SQL Statement.

Lets assume I've a table containing three columns ContactID (Primary Key),
Firstname and Lastname.

I would like to write a stored procedure which returns me the first ten
records and increments an outside variable each time it runs.
E.g If I run it the first time I pass the variable as 0 and it will return
me the first ten records and returns the variable value 1.
When run a second time, I will pass the variable as 1 and it will return me
records 11-20 and sets the variable to 2 and so on...

The difficult thing is how to tell to return me records 11-20. I can't use
the ContactID as someone might have deleted a row and e.g. ContactID 18 is
missing. In this case I only would get 9 rows returned. It always should be
ten.

Thanks very much for your time and efforts!

Kind Regards,

Martin

"There are 10 types of people in this world: Those that understand binary
arithmetic, and those that don't."

Certainly check out the links Aaron and Jacco referred to for a comprehensive
treatment of the topic.

CREATE TABLE Contacts
(
contact_id INT NOT NULL PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL
)

-- The first page is 0
CREATE PROCEDURE NextContacts
@contact_page_number INT OUTPUT,
@number_of_contacts INT = 10
AS
SELECT C.contact_id, C.last_name, C.first_name
FROM (SELECT C1.contact_id, COUNT(*) AS rank
FROM Contacts AS C1
INNER JOIN
Contacts AS C2
ON C2.contact_id <= C1.contact_id
GROUP BY C1.contact_id) AS CC
INNER JOIN
Contacts AS C
ON CC.rank > @contact_page_number * @number_of_contacts AND
CC.rank <= (@contact_page_number + 1) * @number_of_contacts AND
C.contact_id = CC.contact_id
ORDER BY CC.rank ASC
SET @contact_page_number = @contact_page_number + 1

Regards,
jag