Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Retrieve n rows only (Read 6905 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Retrieve n rows only
28.12.05 at 09:14:34
 
Jorma from Finland asked the following question:
 
Question:
Is there any easy way to accept and use only one row of result set, if there unexpectedly is more than one row?
 
Answer:
It is easy to limit the rows returned by a query - a select statement. You can limit the number of rows to one or any other number. The technique depends on which database you use.
  • Oracle databases ; select ... where rownum <= n
    In Oracle databases you use the rownum pseudo column. If you return rows from the emp table using select * from emp you can use the rownum pseudo column in the where clause to limit the number of rows. In the following example I will only retrieve the first 10 rows: select * from emp where rownum <= 10. If you only need the first row you execute select * from emp where rownum <=1
  • SQL Server and Access (and others?) ; select top n ...
    In SQL Server and Access you use the top clause to specify how many rows you want. If you return rows from the publishers table using select * from publishers you can use the top clause to limit the number of rows. In the following example I will only retrieve the first 10 rows: select top 10 * from publishers.  
  • MySQL and PostgreSQL; select ... limit n
    In MySQL and PostgreSQL you use the limit clause. If you return rows from the authors table using select * from authors you can use the limit clause to limit the number of rows. In the following example I will only retrieve the first 10 rows: select * from authors limit 10.
  • Informix and Firebird; select first n ...
    In Informix and Firebird you use the first clause to specify how many rows you want to return. If you return rows from the orders table using select * from orders you can use the first clause to limit the number of rows. In the following example I will only retrieve the first 10 rows: select first 10 * from orders
  • DB2 UDB; select ... fetch first n only
    In DB2 you use the fetch first clause to specify how many rows you want to return. If you return rows from the org table using select * from org you can use the fetch first clause to limit the number of rows. In the following example I will only retrieve the first 10 rows: select * from org fetch first 10 only

 
Please note that the list of databases supporting the techniques is not complete. If you have a database that is not listen and you you know which technique to use please reply to this post to let us all know.
 
I am trying to use this technique in the SQL*XL SQL editor. When you choose a table and select to create a statement to sample rows, SQL*XL will use the above techniques in the SQL to generate the correct statement.
Back to top
 
« Last Edit: 04.01.06 at 14:28:48 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1