Limiting output as with Oracle's LIMIT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Limiting output as with Oracle's LIMIT

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-29 : 12:09:02

SQLsearcher writes "Dear SQLteam

I read the manual (online and offline). I read the FAQ. And still I do not know how to write a stored procedure (sp_limitNM) that results in M rows starting at row N, when I feed it N and M (EXEC sp_limitNM @N = 5, @M = 10).

I tried SELECT TOP, SET ROWCOUNT, SELECT ... WHERE ... NOT IN, SELECT ... MINUS ... SELECT.

I know Oracle, and MySQL have a function called LIMIT N, M, which does exactly what I want.

Technical data:
Windows 2000 Professional
MS SQL Server 2000

Kind regards, SQLsearcher

PS Happy Holidays"

SQLsearcher
Starting Member

47 Posts

Posted - 2002-01-02 : 04:24:54

Dear graz

Thank you for your quick and clear response. Unfortunately it does not do what I want it to do.

The value of TOP must be a variable. According to the online documentation, you can only parameterize @@variables. So I tried ROWCOUNT. This still does not work when you use a composed primary key (existing of multiple attributes).

Kind regards, SQLsearcher

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 07:43:32

Try doing it with Temp table's.

Store the result of first select top in a temp table , on that run your next requiremnt.

This should do what you want, but offcourse it will be more time consuming .

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-01-02 : 08:42:52


DECLARE @sql NVARCHAR(4000)

SELECT TableAID, Column2, Column3 INTO #stage FROM TableA ORDER BY TableAID

SET @sql = 'DELETE FROM #stage WHERE TableAID IN (SELECT TOP ' + 
@START + ' TableAID FROM #stage ORDER BY TableAID'
EXEC sp_executesql @sql

SET @sql = 'SELECT TOP ' + @END + ' * FROM #stage ORDER BY TableAID'
EXEC sp_executesql @sql

DROP TABLE #stage

I wouldn't suggest doing this though :-p It would be smarter to limit the records in a recordset if you are using ADO to connect to the db. You can do something like:


rs.Open sql, cn
rs.Move lngStart
i = 0
Do While Not rs.EOF
 If i > lngEnd Then Exit Do

 ' Do something with your row...

 i = i + 1
 rs.MoveNext
Loop

- Onamuji

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 09:32:27

Onamji from where did you got the idea that Sqlresearches is looking for a VB Code , i think he mentioned doing it in Sql Server using Stored Procedure

.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/02/2002 09:33:04

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-02 : 10:29:51

quote:

Dear graz

Thank you for your quick and clear response. Unfortunately it does not do what I want it to do.

The value of TOP must be a variable. According to the online documentation, you can only parameterize @@variables. So I tried ROWCOUNT. This still does not work when you use a composed primary key (existing of multiple attributes).

Kind regards, SQLsearcher

Then I think you are stuck with dynamic SQL. http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic+sql

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-02 : 11:02:38

Wait. Can you post the code you're using for ROWCOUNT? That should work.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

SQLsearcher
Starting Member

47 Posts

Posted - 2002-01-03 : 04:52:53

quote:

Wait. Can you post the code you're using for ROWCOUNT? That should work.

Dear graz (and others)

Below you can find my SP-code with ROWCOUNT.

quote:

CREATE PROCEDURE sp_limitRowcount
@firstRow INTEGER=1,
@numberOfRows INTEGER=0
AS
SET ROWCOUNT @numberOfRows
SELECT EmployeeID, Gender, Salary
FROM Employees
WHERE EmployeeID NOT IN (SET ROWCOUNT @firstRow
SELECT EmployeeID
FROM Employees)
GO

It does not work because (as far as I know) you can not SET ROWCOUNT in a WHERE-clause.
Even if it would work. It is still not usefull to me, I have to use it with a composed primary key.

The best way to solve it is to create a view with all the columns I want and add an extra index. Then I can use a WHERE-clause with parameters using this index. A drawback is this solution is probably slower than requesting everything and looping through the output.

Suggestions are still welcome.

Greetz, SQLsearcher

izaltsman
A custom title

1139 Posts

Posted - 2002-01-03 : 08:45:40

Looks like you want to pull the records of all employees that are not among the first N records.
I haven't tested, but something along these lines would probably work.

CREATE PROCEDURE sp_limitRowcount
@firstRow INTEGER=1,
@numberOfRows INTEGER=0
AS
SET ROWCOUNT @numberOfRows

SELECT src.EmployeeID,src.Sex, src.Salary
FROM
Employees as src
INNER JOIN
(SELECT e.EmployeeID
FROM
Employees e INNER JOIN Employees x
ON e.EmployeeID > = x.EmployeeID
GROUP BY e.EmployeeID
HAVING COUNT(x.EmployeeID) > @firstRow
) as qual
ON src.EmployeeID = qual.EmployeeID

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-01-03 : 12:22:07

am I missing the point...or is this a variation of the "WHAT'S AFTER TOP?" problem....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-01-03 : 12:29:45

ignore last post....the new year has scrubbed my memory...Graz's link...was WHAT AFTER TOP.

SQLsearcher
Starting Member

47 Posts

Posted - 2002-01-07 : 04:30:17

Dear izaltsman

Your method works (after changing Sex into Gender). Yet in case of a huge table, I think it is slower than our old solution. So it needs testing and tuning. Thank you.

Dear Andrew Murphy

The difference with Graz's "What's after TOP?" is, I want to use TOP as a parameter, which is impossible.

Kind regards, SQLsearcher

Rafiq
Starting Member

25 Posts

Posted - 2002-01-07 : 05:07:25

Hi,

I will try to solve this problem within a query, Now i have one small solution of it.

USE Pups
Go

Select Pub_Id, Pub_Name, City, State, Country,(Select Count(Pub_Id) From publishers a Where a.Pub_id <= b.pub_Id) As RowNum
Into #Tmp_Publishers
From publishers b

Go

Select * From #Tmp_Publishers Where RowNum >= 3 And RowNum <= 6

(The above query returns rows between 3 and 6)

It will works like Oracle's ROWNUM. You can parameterized it.

Regards,

Rafiq
------------------------------------
If you think, you can do anything...

SQLsearcher
Starting Member

47 Posts

Posted - 2002-01-08 : 03:13:57

Dear Rafiq

Your solutions works. The drawback is to find the first N rows, you have to find the first 1 rows, the first 2 rows, ... , the first N - 1 rows and finally the first N rows. This leads to a table with 1 + 2 + ... + N = N * (N + 1) / 2 rows from which all but the last N are useless. So this solution is not scalable. It would be faster to create this "counter" with a SELECT INTO query in a table with and index.

Kind regards, SQLsearcher

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources