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
Best answer by Tomas Ruderfelt
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
+18Hi @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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
