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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-08-31 : 16:17:11
|
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. |
|
|
Rohit_thakare
Starting Member
1 Post |
Posted - 2002-01-31 : 12:45:37
|
| 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.ThanksRohit |
 |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-31 : 13:13:42
|
| 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 |
|
|
|
mohamedyousuff@yahoo.com
Starting Member
24 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-08 : 09:20:29
|
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 |
|
|
|
mohamedyousuff@yahoo.com
Starting Member
24 Posts |
Posted - 2002-05-09 : 03:08:55
|
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
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-09 : 04:18:54
|
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 .-------------------------------------------------------------- |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-09 : 08:58:54
|
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! |
|
|
|
objectuser
Starting Member
14 Posts |
Posted - 2004-05-25 : 15:49:20
|
| 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: 10Page 2: 10Page 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 |
|
|
|
|
|
|
|
|
Article: What's after TOP?