HELP: Selecting a Specific number of Records from Record Set 
Author Message
 HELP: Selecting a Specific number of Records from Record Set
How do you select a specific number of records from a recordset?
For printing book requests for a publisher, we only want to print seven
book titles for each unique publisher at a time.

For example, if I have the query:

    SELECT BookISBN,BookTitle,CoverType FROM tblBooks
    WHERE BookPubID = 'Addison'

How would I select just 7 of those (I would then mark those
books as being sent out).

Thanks

Tony Jones



Fri, 10 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set

Quote:
>How do you select a specific number of records from a recordset?

Just use :

set rowcount 7

to return just 7 rows. You need to do a 'set rowcount 0' afterwards as the
command persists for the duration of that connection.

Julian Watson



Fri, 10 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set
Add the condition AND rownum <= 7 and you should get only the first 7 rows
where BookPubID = 'Addison'
    Good luck!
Quote:

>How do you select a specific number of records from a recordset?
>For printing book requests for a publisher, we only want to print seven
>book titles for each unique publisher at a time.

>For example, if I have the query:

>    SELECT BookISBN,BookTitle,CoverType FROM tblBooks
>    WHERE BookPubID = 'Addison'

>How would I select just 7 of those (I would then mark those
>books as being sent out).

>Thanks

>Tony Jones




Sat, 11 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set
I believe this works in Sybase, but not in Oracle.


Quote:

>>How do you select a specific number of records from a recordset?

>Just use :

>set rowcount 7

>to return just 7 rows. You need to do a 'set rowcount 0' afterwards as the
>command persists for the duration of that connection.

>Julian Watson



Sat, 11 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set

Wayne or Donna Weiselogel schrieb:

Quote:

> Add the condition AND rownum <= 7 and you should get only the first 7 rows
> where BookPubID = 'Addison'
>     Good luck!

Yes, you will get the first 7 rows oracle finds, but no ordering is
possible. If you want some ordering look for Jurij Modic's TOP X (or TOP
N) solution in dejanews archive.

HTH
Matthias
--

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden.
Charles H. Duell, Leiter des US Patentamtes bei seinem Rcktritt 1899



Sat, 11 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set

Quote:
>I believe this works in Sybase, but not in Oracle.

Mmm

And I thought that this was a SQL server newsgroup !!

Julian Watson



Sat, 11 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set
Julian,

Quote:
>And I thought that this was a SQL server newsgroup !!

The original message and subsequent replies - including yours - were
cross posted to three quite diverse groups:

  comp.databases.oracle.misc
  microsoft.public.fox.fox2x.queries-sql
  microsoft.public.sqlserver.programming

so it is not surprising when the discussion gets a bit confused.

Roy



Sun, 12 Aug 2001 03:00:00 GMT
 HELP: Selecting a Specific number of Records from Record Set
You need to know something.  Unless you're fetching the first rownum (i.e.,
rownum = 1, rownum < n)  as part of your query, you will return no rows.
Quote:

> How do you select a specific number of records from a recordset?
> For printing book requests for a publisher, we only want to print seven
> book titles for each unique publisher at a time.

> For example, if I have the query:

>     SELECT BookISBN,BookTitle,CoverType FROM tblBooks
>     WHERE BookPubID = 'Addison'

> How would I select just 7 of those (I would then mark those
> books as being sent out).

> Thanks

> Tony Jones




Mon, 20 Aug 2001 03:00:00 GMT
 
 [ 12 post ] 

 Relevant Pages 

1. HELP: Selecting a Specific number of Records from Record Set

2. Select specific number of records

3. selecting specific records from found set ??

4. Get X records before and Y records after a specific record

5. Selecting a set number of random records

6. randomly select records from a record set

7. Randomly selecting records from a record set

8. Select multiple top 5 record from single record set

9. Find certain Number of Records / Print only certain number of records

10. select the top number records where the qty would sum to a given number

11. HELP: Determining number of records in result set

12. Record Set Guru's - Can't Create Record Set error 429


 
Powered by phpBB® Forum Software