SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: What's after TOP?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/31/2000 :  16:17:11  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Lindsey writes "I know you can, quite easily, select the TOP 15 or 20 or 300 rows of a table, but is there a way to select 16-19? or 21-299? There doesn't seem to be one, but perhaps the Guru knows a secret trick..."

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 09/05/2000 :  18:24:40  Show Profile  Reply with Quote
Get them in order

Could you not just do this:

SELECT TOP 10 Id
FROM Table1
WHERE Id NOT in (SELECT TOP 10 Id
FROM Table1
ORDER BY Quantity DESCENDING)
ORDER BY Quantity DESCENDING

It's the same, I'm pretty sure. I Just did this in SQL Servers pubs sample DB:

SELECT TOP 10 *
FROM authors a
WHERE au_id NOT IN
(
SELECT TOP 10 au_id
FROM authors a2
ORDER BY au_lname
)
ORDER BY au_lname

Richard
chadich@yahoo.com

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/07/2000 :  13:28:57  Show Profile  Reply with Quote
selecting paged result sets in a stored procedure with top and subqueries

We have found that this does not actually work in all situations and if it did, you would have to have a separate query for each page of the data.

SELECT TOP 10 Id
FROM Table1
WHERE Id in (SELECT TOP 20 Id
FROM Table1
ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING

The main query actually re-orders the subquery because the orderby gets processed before the top. This is evident if you view the execution plan for the query.

A solution to this problem that scales to any rowcount and page size is slightly more complicated but not too much.

-- you will need know the values of the following 2 variables, probably passed into a stored procedure

declare @pagesize int ' number of items on a page
declare @currentpage int ' current page

declare @rowcount int ' total items in full result set

declare @subquerytop int ' variable to hold the calculated amount to return in the subquery

declare @sqlcmd nvarchar(1024) ' string to hold query (sql 7.0 does not allow variables for TOP so you have to build and execute a string with exec or sp_executesql.

-- calculate the rowcount
SELECT @rowcount = count(*) FROM Table1

-- calculate the items to return in subquery
set @subquerytop = @rowcount - ( ( @currentpage - 1 ) * @pagesize)

set @sqlcmd = '
SELECT TOP ' + cast(@pagesize as nvarchar(16)) + ' Id
FROM Table1
WHERE Id in
(
SELECT TOP ' + cast(@subquerytop as nvarchar(16)) + ' Id
FROM Table1
ORDER BY Quantity DESC
)
ORDER BY Quantity ASC'

exec (@sqlcmd)

CJL

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/07/2000 :  17:59:28  Show Profile  Reply with Quote
Still don't get why not..

Hi, appreciate your response... yes, I understand that your query (more or less) inverts the subquery and selects the top 10 from it... but could you explain further why my query wouldn't be the functional equivalent (except in a different sort order)??

I mean here are the implementations of what I'm saying and what you're saying in the pubs example database in SQL Server 7.0. Mine comes out as slightly more expensive, but returns records in the desired order. Could you show me an example in the pubs database that doesn't work, as you've observed?? Also I'm not sure what you meant: "you would have to have a separate query for each page of the data.", again I don't see how it's different from your query.

Thanks,
Richard

SELECT TOP 10 *
FROM authors a
WHERE au_id NOT IN
(
    SELECT TOP 10 au_id
    FROM authors a2
    ORDER BY au_lname ASC
)
ORDER BY au_lname ASC

SELECT TOP 10 *
FROM authors a
WHERE au_id IN
(
    SELECT TOP 20 au_id
    FROM authors a2
    ORDER BY au_lname ASC
)
ORDER BY au_lname DESC

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/08/2000 :  10:42:34  Show Profile  Reply with Quote
yes, but

I was refering to the query in the original article. I included the orignial query in my reply to clarify. Your query does work fine and returns the correct set of data as you page through it in the blocks of ten. If you compare those results to the original queries results, you should see that it does not return the correct set of data. I was attempting to solve the problem in a scalable way that could be inserted into a stored procedure with pagesize and current page parameters so that any pagesize, page combination would result in the correct data set. You have to do a small amount of extra work because Sql 7.0 does not support variables in the TOP command. You could not, for instance, do this:

SELECT TOP @topnumber * FROM Table1

Hopefully, Sql 2000 will correct this.

CJL

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/10/2000 :  15:57:09  Show Profile  Reply with Quote
Not really an Effective Query

Hi, I've read the #2 command
I think this is not really an effective query. Why ?, here's the case you might want to figure out.
When you want to go to first page it's not a big deal because you only select:
------------------------------
SELECT TOP 10 Id
FROM Table1
WHERE Id in (SELECT TOP 0 Id
FROM Table1
ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING
------------------------------

and for the next 10, 20, 30 or (n) records:
------------------------------
SELECT TOP 10 Id
FROM Table1
WHERE Id in (SELECT TOP [10,20,30,...] Id
FROM Table1
ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING
------------------------------

Imagine if you have 100,000 records, and you want to select last 10 record you will do this:
------------------------------
SELECT TOP 10 Id
FROM Table1
WHERE Id in (SELECT TOP 99990 Id
FROM Table1
ORDER BY Quantity DESCENDING)
ORDER BY Quantity ASCENDING
------------------------------

Wow, it's just too many to select.

Currently I am trying to figure this problem out. So I don't have the solution at the moment

Thank you.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/09/2001 :  12:01:46  Show Profile  Reply with Quote
Well, I hope this helps.

Hi gentlemen,

I built this little query taking the idea from the SQL Guru. You can include this query in your ASP or create a stored procedure and pass it parameters and you can get the required rows .

/*******************************************************/
Declare @i int,
@Get int,
@NoOfRows int,
@SQL nvarchar(500)
Set @NoOfRows = 10
Set @Get = 2
Set @i = 20 * @Get

Set @SQL = N'Select * from Customer where Cust_ID in ('
Set @SQL = @SQL + N'Select top ' + Cast(@NoOfRows as varchar(10)) + N' Cust_ID from Customer where Cust_ID in('
Set @SQL = @SQL + N'Select top ' + Cast(@i as varchar(8)) + N' Cust_ID from customer order by Cust_ID asc)'
Set @SQL = @SQL + N'order by Cust_ID desc)'

Exec sp_ExecuteSQL @SQL
/*******************************************************/

Go to Top of Page

Rohit_thakare
Starting Member

1 Posts

Posted - 01/31/2002 :  12:45:37  Show Profile  Reply with Quote
Can you please write the above query if you were using Sybase. Also I don't need the first n rows. I need the nth row.

Thanks

Rohit

Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 01/31/2002 :  13:13:42  Show Profile  Reply with Quote
Rohit, you are on Site dedicated to Sql Server.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is
Go to Top of Page

mohamedyousuff@yahoo.com
Starting Member

India
24 Posts

Posted - 05/08/2002 :  08:57:54  Show Profile  Reply with Quote


I think this question is better to be posted here. Please follow this link and get me some solution.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15613

I started a discussion on the same in another forum (outside of this site) but no satisfactory result yet.

The link is

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4082&FORUM_ID=23&CAT_ID=2&Topic_Title=Parameter+to+the+TOP+operator&Forum_Title=General

Gurus, please help me out of the situation.
(Hmmm... one of my colleagues made a point on this issue; that Oracle has an edge over sqlserver on this issue since it has some 'LIMIT' operator to do this job automatically without any performance problem)



S.Mohamed Yousuff



Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 05/08/2002 :  09:20:29  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
(Hmmm... one of my colleagues made a point on this issue; that Oracle has an edge over sqlserver on this issue since it has some 'LIMIT' operator to do this job automatically without any performance problem)

Hey, there's nothing stopping you from using Oracle! Except price, excessive hardware requirements, mandatory consultation fees from Oracle, low performance on Windows OS's compared to SQL Server...

...wait, just remembered: several days to install it, weeks to configure and tune it, probably another few weeks to migrate the data from SQL Server to Oracle (using DTS could accomplish this in less than a day...but DTS is a MICROSOFT product!)

Edited by - robvolk on 05/08/2002 09:22:37
Go to Top of Page

mohamedyousuff@yahoo.com
Starting Member

India
24 Posts

Posted - 05/09/2002 :  03:08:55  Show Profile  Reply with Quote


I am really sorry to point at Oracle in SQLTeam.

quote:

quote:
(Hmmm... one of my colleagues made a point on this issue; that Oracle has an edge over sqlserver on this issue since it has some 'LIMIT' operator to do this job automatically without any performance problem)

Hey, there's nothing stopping you from using Oracle! Except price, excessive hardware requirements, mandatory consultation fees from Oracle, low performance on Windows OS's compared to SQL Server...

...wait, just remembered: several days to install it, weeks to configure and tune it, probably another few weeks to migrate the data from SQL Server to Oracle (using DTS could accomplish this in less than a day...but DTS is a MICROSOFT product!)

Edited by - robvolk on 05/08/2002 09:22:37



Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 05/09/2002 :  04:18:54  Show Profile  Reply with Quote
Nah! you havent got the point yousuff, its not what you posted has disturbed Rob. its the Oracle product that disturbs . though they have been in databases from over two decades, not much of changes. take for instance Sql server from its earlier product to now , there have been revolutionary changes(Compare the time of Sql server and Oracle's first release to now). you will find how much R&D has been done for imporvement of Sql Server. and with the coming of Yukon things will be very different.

you find some feature's in Dbase which aren't there in Sql server and Oracle that doesnt mean that Sql Server and ORacle are lesser to Dbase.

Another thing, if you are not satisfied with the answers here am sure you wouldnt find a better answer anywhere else .



--------------------------------------------------------------
Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 05/09/2002 :  08:58:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
Sorry mohamedyousuff, I'm not annoyed at you, and I'm not blasting Oracle. I use Oracle every now and again and there are several things that I like about it.

The point I was trying to make is that IMHO it serves no purpose to compare a product you are using to a product you ARE NOT using or CANNOT use. If you're "stuck" with SQL Server, you should find a way to make SQL Server work, instead of lamenting that "Oracle does this, it's a better product". There are also many factors in choosing a database product other than the features it provides.

To really belabor the point a number of people consider Oracle the "Rolls-Royce" of database products, and will call SQL Server the "Mercedes" or "Cadillac" or, if they really hate Microsoft, the "Yugo" of database software. If all you want to do is drive somewhere, do you need to spend a quarter of a million dollars for a Rolls-Royce, when you can drive (in exquisite luxury) in a Mercedes for $65,000?

BTW, these prices I mentioned are, coincidentally, VERY CLOSE to what you would pay for equivalent Oracle and SQL Server setups, respectively. I have no problem justifying to people that I'm getting at least 90% of the performance of Oracle, if not better, for one-fourth the price!

Go to Top of Page

objectuser
Starting Member

14 Posts

Posted - 05/25/2004 :  15:49:20  Show Profile  Reply with Quote
I recently had to do this same thing on my project. I started with the original solution to this question, with an extra twist of re-sorting the values into the original order.

The problem I had with this query is that it doesn't work for the last page. If the last page should only have a few items on it (any number less than the page size), this query will still return enough rows to fill a page.

For example, if you have 23 items total, and your page size is 10, the pages will be of size:

Page 1: 10
Page 2: 10
Page 3: 10!

The last page should obviously only include 3 items. This solution is just a moving window, always of size 10, which is not what you want for paging.

I certainly haven't found an efficient single-query solution for this (or one I think is efficient).

It seems that the correlated subquery solution does work (and I found it quite clever), but it's also quite expensive (because it is correlated), and so perhaps not the best solution for an OLTP application. Further, it will also only work in a situation where the target query preserves a unique key.

The set difference solution also seems to work. I don't think the issue raised concerning it is that big of a deal because I don't expect users to want to page past 5 or 6 pages (certainly never the 1000th page), and I don't expect to allow them to in any case. However, it does require you effectively perform your query twice, which bothers me (maybe it shouldn't ...).

I ended up just doing a top to limit the rows and then forwarding to the right page in my cursor. Not optimal, but I think it's a better solution than the other two (though I haven't been able to test it under load, yet), assuming I don't let the users page too far.

I'd welcome any comments, either in support or opposition to the above conclusions.

--Kevin
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000