Retrieve Top N records from a query

---

Question: How can I retrieve the Top N records from a query?
For example, what if I wanted to retrieve the first 3 records from my query results. How can I do this?

Answer: To retrieve the Top N records from a query, you can use the following syntax:

SELECT *
FROM (your ordered query) alias_name
WHERE rownum <= Rows_to_return
ORDER BY rownum;

For example, if you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 3 records from the suppliers table, sorted by supplier_name in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_name DESC) suppliers2
WHERE rownum <= 3
ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in ascending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id) suppliers2
WHERE rownum <= 5
ORDER BY rownum;

If you wanted to retrieve the first 5 records from the suppliers table, sorted by supplier_id in descending order, you would run the following query:

SELECT *
FROM (select * from suppliers ORDER BY supplier_id DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum;

EXISTS Condition

SQL: EXISTS Condition

---

The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );

The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Note:

These EXISTS queries are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use Exists.

Example #1:

Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS:

The EXISTS condition can also be combined with the NOT operator.
For example,

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement:

The following is an example of a delete statement that utilizes the EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS
  (select *
    from orders
    where suppliers.supplier_id = orders.supplier_id);

Example #4 - UPDATE Statement:

The following is an example of an update statement that utilizes the EXISTS condition:

UPDATE suppliers
SET supplier_name =	( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS   ( SELECT customers.name     FROM customers     WHERE customers.customer_id = suppliers.supplier_id);

Example #5 - INSERT Statement:

The following is an example of an insert statement that utilizes the EXISTS condition:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

RANK() VS DENSE_RANK()

The RANK()function in SQL Server returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() function in SQL Server returns the position of a value within the partition of a result set, leaving no gaps in the ranking where there are ties.

Let us understand this difference with an example and then observe the results while using these two functions:

We will run two queries, one using RANK() and the other using DENSE_RANK() and observe the difference in the results. We will be using the ORDERS table of the NORTHWIND database to demonstrate the difference. The query will fetch the list of Customers ordered by the highest number of orders each has placed.

Using the RANK() function

SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt

FROM (SELECT CustomerID, COUNT(*) AS TotCnt

FROM Orders Group BY CustomerID) AS Cust

OUTPUT



As shown in the results above, while using the RANK() function, if two or more rows tie for a rank, each tied rows receives the same rank, however with gaps in the ranking where there are ties. For example, Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 6 instead of 5, because there are two rows that are ranked higher at 4.

Using the DENSE_RANK() function

SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt

FROM (SELECT CustomerID, COUNT(*) AS TotCnt

FROM Orders Group BY CustomerID) AS Cust

OUTPUT



As shown in the results above, while using the DENSE_RANK() function, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties. Customers 'FOLKO' and 'HUNGO' have the same number of orders(i.e. 19), so they are both ranked 4. The Customers with the next highest order(HILAA, BERGS, RATTC) are ranked number 5. This is not the same as the RANK() function where the Customer with the next highest number of orders were ranked number 6.

Well I hope after seeing these example, you will understand the difference between the RANK() and DENSE_RANK() and will know where to use what.

OUTBOUND_INTERFACE

REQUIREMENT:

Oracle Inventory

3    Topical Essay

GE Infrastructure Sensing needs to procure Inventory details from the Inv for payment details which we have done for particular Supplier. Inv Outbound interface program will be developed to transfer the data from oracle INV base tables into the OFM (Legacy) flat file.

3.1   Basic Business Needs

Company has to make all the Transaction in OFM (Legacy), GE needs a data file which is going to store in the legacy database.

This Program should bring the data from Inventory which has been entered between the Two Organizations.

And Item should be Purchasable item.

3.1.1   Assumptions

This design assumes that the following information will be maintained in Chinese language:

1.       Invorganization ID

2.       Invorganization Name

3.       ItemID

4.       Item

5.       Itemdescription

6.       Catetgory

7.       ItemUOM

________________________________________________________________________________

3.2   Inv Outbound output file

Report output should show the following information in the Output file.

No Of Records Transferred into file   : No of Records which are transferred to the Flat file.

User Name                                         : Name of the User Who has submitted

Resonsibility                                     : Name of the Responsibility from which report has submitted

Date of submitted                                                  : Request submission Date

3.2.1   Report Parameters

You run the ‘Inv Outbound Interface from the Standard Report Submission form. 

Enter the following parameters.

Sq	Parameter	Description	Value set	Default Type	Default Value	Required	Displayed	Prompt	Token
1	Org_ID From	Org_ID	None	Profile	Current Organization	Y	Y	ORG ID	P_org_id
2	Org_ID To	Org_ID	None	Profile	Current Organization	Y	Y	ORG ID	P_org_id

1.1.1    Configuration Dependencies (Optional)

Configuration	Configuration Setting
Register Concurrent Program	_Inv Outbound Interfaces
Register Concurrent Executable	XX_GE_INV
Responsibilities	RESP
Request Security Group	XX_GE_Payments
Data Group	Standard

3.2.2   Report Headings

4    Technical Overview

Use this section to describe the technical approach, high-level algorithms, process flows, etc.  Discuss possible solutions to technical challenges.  The detailed designs for forms and programs will provide the details for the general overview presented here.

Used the following tables to get the Customer and the invoive, item details.

MTL_SYSTEM_ITEMS_B

ORG_ORGANIZATION_DEFINITIONS

MTL_CATEGORIES

MTL_ITEM_CATEGORIES

5    Considerations

If any Oracle Applications tables change in future versions of Oracle Purchasing, components of this customization may also require changes to work correctly.  Evaluating the impact of such changes on custom programming is the responsibility of the company, although additional consulting services can be contracted to assist prior to upgrading.
Open and Closed Issues for this Deliverable

5.1.1   Open Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

5.1.2   Closed Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date
1	Chinesse output	Developer can develop program as like English It will generate Chinese output Automatically , Since we have implemented UTF Instance.	Developer

CREATE OR REPLACE procedure XX_INV_Out1(Errbuf OUT varchar2,
                                       Retcode ouT varchar2,
            f_id    in number,
            t_id    in varchar2) as
cursor c1 is select
    msi.segment1 item,
    msi.inventory_item_id Itemid,
    msi.description  itemdesc,
    msi.primary_uom_code Uom,
    ood.organization_name name,
    ood.organization_id   id,
    mc . segment1||','||mc.segment2 Category
    from
    mtl_system_items_b           msi,
    org_organization_definitions ood,
    mtl_item_categories          mic,
    mtl_categories               mc
    where
    msi.organization_id       = ood.organization_id
    and msi.inventory_item_id = mic.inventory_item_id
    and msi.organization_id   = mic.organization_id
    and mic.category_id       = mc.category_id
    and msi.purchasing_item_flag = 'Y'
    and msi.organization_id between f_id and t_id;
x_id     utl_file.file_type;
l_count  number(5) default 0;
begin
          x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
           --select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item    ||'-'||
        x1.itemid  ||'-'||
        x1.itemdesc||'-'||
        x1.uom   ||'-'||
        x1.name   ||'-'||
        x1.id   ||'-'||
        x1.category   );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name  '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
  fnd_file.put_line(fnd_File.log,'invalid operation');
  utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
  fnd_file.put_line(fnd_File.log,'invalid path');
  utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
  fnd_file.put_line(fnd_File.log,'invalid mode');
  utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
  fnd_file.put_line(fnd_File.log,'invalid filehandle');

  utl_file.fclose_all;
WHEN utl_file.read_error THEN
  fnd_file.put_line(fnd_File.log,'read error');
  utl_file.fclose_all;
WHEN utl_file.internal_error THEN
  fnd_file.put_line(fnd_File.log,'internal error');
  utl_file.fclose_all;
WHEN OTHERS THEN
  fnd_file.put_line(fnd_File.log,'other error');
  utl_file.fclose_all;
End XX_INV_Out1;