Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 18th, 2003, 05:37 AM
Authorized User
 
Join Date: Jun 2003
Location: Naples, Italy, .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT TOP

Hi,
if i want to select the first 20 records from a table, i can write:
"SELECT TOP 20 data1, data2 from table"

Example:
1. david
2. George
...
20. Philip

How can i do to select the second 20 records from tha same table?

Example:
21. John
22. Jack
...
40. Josephine

Thank you
Reply With Quote
  #2 (permalink)  
Old June 18th, 2003, 07:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

There are proprietary solutions to this problem. Oracle, for example, provides a ROWNUM function which numbers the rows of a resultset. You posted this in an SQL Language forum, so an SQL language purist like myself would of course, avoid such proprietary features like the plague. :D

First off, when you say "...i want to select the first 20 records [sic] from a table...", what do you mean by 'first'? The concept of 'first' implies some ordering of the data. That is, that there is a way to determine which row is 'first', 'second', etc. The query you posted does not order the data at all, so the query processor is free to return the results in any order it chooses, and there is no guarantee the order would be the same each time it is run. Indeed, what basis did you use to place John and Jack in the second set of 20? The first set looked like it was alphabetic, but the second set proved that wrong. There is no way for me to determine how you grouped this data into sets of 20 rows.

Thus, although the TOP clause indeed restricts the resultset to 20 rows, you have no way to guarantee which 20 rows that is.

The simplest way to deal with your problem is to handle it in the client, since what you are really asking for is a resolution to a presentation issue, and such presentation issues are best left for the front end. RDBMS's such as Oracle and SQL Server are very good at storing and retrieving data, not so good at presenting it.

Another way is to save the results of your (ordered) query into a temporary table and include a sequence number column in the temporary table along with the rest of your query data. You can assign the sequence number in a variety of ways. In SQL Server, for example, this column could be an identity column which would automatically assign an increasing number to each row as it was inserted into the temporary table. Subsequent queries could then select ranges of rows based on this sequence number column.

The 'purist' way to number rows (not necessarily the best, as you'll see) is to partition the data into a series of subsets. Each subset contains all of the data rows whose sort value (remember that the rows must be sorted for the concept of numbering to have any meaning) is less than the sort value of a given row. The 'sequence number' is then the count of the elements in that subset. A correlated subquery makes this (ahem) easy:
Code:
SELECT
   (COUNT(*) FROM YourTable T2 WHERE T2.orderingcolumn<=T1.orderingcolumn) as seqno,
   orderingcolumn, data1, data2, ...
FROM YourTable T1
ORDER BY orderingcolumn;
This assigns a 'sequence number' to each row, according to the 'orderingcolumn'. Note that this technique does not handle ties well. If the 'orderingcolumn' does not have unique values, then all the equal values of the 'orderingcolumn' will be assigned the same sequence number. Its straightforward to extend the technique to additional columns (like the primary key) to insure that the rows are ordered acording to some unique value or combination of values.

To select ranges of sequence numbers you just repeat the 'counting' subquery in the WHERE clause. Assuming the parameters '@Start' and '@End':
Code:
SELECT
   (COUNT(*) FROM YourTable T2 WHERE T2.orderingcolumn<=T1.orderingcolumn) as seqno,
   orderingcolumn, data1, data2,...
FROM YourTable T1
WHERE (COUNT(*) FROM YourTable T2 WHERE T2.orderingcolumn<=T1.orderingcolumn)
   BETWEEN @Start AND @End
ORDER BY orderingcolumn;
This isn't going to be very fast if you have a large number of rows in your query. In that case, you are probably better off with the temporary table approach.




Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old June 18th, 2003, 07:35 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

I'd be interested to see what the others make to this one.

MySQL gives you a limits clause so select * from table limit 20,30 (or thereabouts)
Far as I am aware you can't do this with SQL Server. What I've done is hold the highest order value and then use this in the where clause.

ie Select top 20 myKey from table1 where myKey>20.

Not very elegant but there you go!

Regards
Graham
Reply With Quote
  #4 (permalink)  
Old June 18th, 2003, 07:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I was so hung up on assigning a sequence number, I forgot the original question. If you don't care about an actual sequence number, and just want rows in groups of, say, 20, then Graham's excellent suggestion of remembering the highest sort value from the prior set and returning the next set where the ordervalue is greater than that value is quite efficient and useful:
Code:
SELECT TOP 20 orderingcolumn, data1, data2, ...
   FROM Yourtable
WHERE Orderingcolumn>@LastGroupsHighestValue;
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old June 18th, 2003, 08:31 AM
Authorized User
 
Join Date: Jun 2003
Location: Naples, Italy, .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

for the moment thank you very much Jeff.

Now I try your solutions and I write you later

Thank you !!

P.S. You're very kind!
Reply With Quote
  #6 (permalink)  
Old August 8th, 2003, 04:35 AM
Authorized User
 
Join Date: Jun 2003
Location: Shah Alam, Selangor, Malaysia.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

how do I select TOp 5 for every item on SQL Transact?

Example:
Item_no CUSTOMER qty
A1001 C001 10
A1001 C002 5
A1001 C003 25
A1001 C004 45
A1001 C005 15
A1002 C002 35
A1002 C006 15
A1002 C005 15
A1002 C004 25
A1002 C001 75


I NEED A RESULT AS :
A1001 C004 45
A1001 C003 25
A1001 C005 15
A1001 C001 10
A1001 C002 5
A1002 C001 75
A1002 C002 35
A1001 C004 25
A1002 C005 15
A1002 C006 15

REGARDS

JANE
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SELECT TOP n rgerald SQL Server 2000 3 May 12th, 2006 04:03 PM
select top problem keyvanjan SQL Server 2000 5 January 7th, 2006 07:11 AM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
SELECT TOP FROM HAVING khatfield29 SQL Language 1 August 23rd, 2004 02:41 PM
SELECT TOP !!!! Jane SQL Language 2 October 17th, 2003 11:22 AM



All times are GMT -4. The time now is 07:51 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.