Skip to main content

Trying to get highest 20 rows of the below query based on “Net Amount in USD” column which is a sum column. Any clues?

 

select 

 INVOICE.IDENTITY as "Identity", 
supplier.vendor_name,
to_char(sum(INVOICE.ACTUAL_NET_DOM_AMOUNT), '$999,999,999') as "Net Amount in USD"

 from &AO.INVOICE 

inner join

&AO.supplier

on &AO.Invoice.identity = &AO.supplier.vendor_no

where   INVOICE.OBJSTATE <> 'Cancelled' 

group by

 INVOICE.IDENTITY,vendor_name
 

Hi @Housseiny, 
You can try several options.
First Order by using the ‘Net Amount in USD’ value.
Then try as below.

ex: 
# Add below to the beginning of the select statement.SELECT TOP (20) PERCENT

Or
# Add below where condition to the SQL Query
WHERE ROWNUM <= 20

Or
#Add below condition to end of the Select Statement
FETCH FIRST 20 ROWS ONLY.

Best Regards
-Kelum
 

Like this, had to remove &AO when I tested but I guess you get the idea:

 

SELECT i.identity "Supplier ID", s.vendor_name "Supplier Name", to_char(sum(i.actual_net_dom_amount), '$999,999,999') as "Net Amount in USD"
FROM invoice i
INNER JOIN supplier s
ON i.identity = s.vendor_no
WHERE i.objstate <> 'Cancelled' 
AND i.party_type_db = 'SUPPLIER'
GROUP BY i.identity,s.vendor_name
ORDER BY sum(i.actual_net_dom_amount) DESC
FETCH FIRST 20 ROWS ONLY

Hi @Housseiny, 
You can try several options.
First Order by using the ‘Net Amount in USD’ value.
Then try as below.

ex: 
# Add below to the beginning of the select statement.SELECT TOP (20) PERCENT

Or
# Add below where condition to the SQL Query
WHERE ROWNUM <= 20

Or
#Add below condition to end of the Select Statement
FETCH FIRST 20 ROWS ONLY.

Best Regards
-Kelum
 

Many thanks Kelum for your prompt feedback

Like this, had to remove &AO when I tested but I guess you get the idea:

 

SELECT i.identity "Supplier ID", s.vendor_name "Supplier Name", to_char(sum(i.actual_net_dom_amount), '$999,999,999') as "Net Amount in USD"
FROM invoice i
INNER JOIN supplier s
ON i.identity = s.vendor_no
WHERE i.objstate <> 'Cancelled' 
AND i.party_type_db = 'SUPPLIER'
GROUP BY i.identity,s.vendor_name
ORDER BY sum(i.actual_net_dom_amount) DESC
FETCH FIRST 20 ROWS ONLY

Many thanks Tomas, worked like a charm

Reply


Terms & ConditionsCookie settings