[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Top 10 invoice amounts in Oracle Payables

Posted on 2009-12-23
8
Medium Priority
?
642 Views
Last Modified: 2013-11-11
I'm trying to create a sql to query the top 10 invoice amounts in descending order. The attached code returns no records.
select invoice_amount from ap_invoices_all where 1=1 group by rownum, invoice_amount having count(rownum)=10 order by 1 desc

Open in new window

0
Comment
Question by:janthonyn
8 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 26115609
Hi janthonyn,

try something like
SELECT * FROM (SELECT invoice_amount, row_number() OVER(PARTITION BY 1 ORDER BY invoice_amount DESC) as rn FROM ap_invoices_all) WHERE rn <= 10)

lwadwell
0
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 26117531
I think it should be a little easier than that, try this:

SELECT invoice_amount
FROM ap_invoices_all
ORDER BY invoice_mount DESC
WHERE ROWNUM <= 10
0
 

Author Comment

by:janthonyn
ID: 26119353
lwadwell
I got the following syntax error with your sql
ORA-00933: SQL command not properly ended
I'm in an Oracle 9i database environment, so probably won't work.
Goodangel:
Shouldn't ORDER BY appear after WHERE in any SQL?
Either way, got the same syntax error asa lwadwell
Neither suggestion works
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:janthonyn
ID: 26119369
Goodangel:

When I switched the order of the WHERE and ORDER BY clauses, I got records. The problem with this is that the records that I got were not the top 10 in amount, they were the first 10 entries in that table. It's not the solution to my question.
0
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 920 total points
ID: 26121433
SELECT INVOICE_AMOUNT FROM (SELECT INVOICE_AMOUNT FROM ap_invoices_all ORDER BY invoice_mount DESC) WHERE ROWNUM <= 10;


 
0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 920 total points
ID: 26121476
it should work in 9i.
I had an extra bracket ")" on the end accidentally.

SELECT * FROM (SELECT invoice_amount, row_number() OVER(PARTITION BY 1 ORDER BY invoice_amount DESC) as rn FROM ap_invoices_all) WHERE rn <= 10;
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 160 total points
ID: 26133192
Since rownum is a pseudocolumn of the result set of a query and not of a table, you can not use "rownum =" for anything other than 1. Just replace the "where rownum = 10" with "where rownum <= 10" in your query and you should get what you want.
0
 

Author Closing Comment

by:janthonyn
ID: 31669607
Never used OVER nor PARTITION BY  before.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question