469,126 Members | 1,255 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Help with Order By AND ROWNUM by a student

I've tried several scripts and NONE of them have worked. I need to apply the Order By first, then limit to the top 10 Rows. Here are two I've tried, with both i'm getting a "command not properly ended" message:


--Question 8c 4 columns from Invoices table with alias' and two calculated columns and filter and sort with ROWNUM

Expand|Select|Wrap|Line Numbers
  1. SELECT  invoice_number AS "Number", invoice_total AS "Total", payment_total + credit_total AS "Credits", invoice_total - (payment_total + credit_total) AS "Balance Due"
  2. FROM  invoices
  3. WHERE invoice_total - (payment_total + credit_total) > 500
  4. ORDER BY 4 DESC
  5.   (SELECT *
  6.   WHERE ROWNUM = 10)

Expand|Select|Wrap|Line Numbers
  1. SELECT  invoice_number AS "Number", invoice_total AS "Total", payment_total + credit_total AS "Credits", invoice_total - (payment_total + credit_total) AS "Balance Due"
  2. FROM  invoices
  3. WHERE invoice_total - (payment_total + credit_total) > 500 
  4.       ( SELECT * from invoices
  5.       ORDER BY invoice_total - (payment_total + credit_total) DESC
  6.       );
  7. WHERE ROWNUM = 10;
Oct 25 '12 #1
2 1677
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code.

The correct syntax is this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (
  3.    SELECT *
  4.    FROM someTable
  5.    ORDER BY someField
  6. )
  7. WHERE rownum <= 10;
Oct 25 '12 #2
WOW! You are amazing.....that answer worked like a charm....don't know why my book doesn't lay it out in those terms....
Sorry about the incorrect format, I will get it right next time.
Thanks!
Oct 25 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Muzamil | last post: by
13 posts views Thread by Joe Black | last post: by
3 posts views Thread by Paul T. Rong | last post: by
3 posts views Thread by John | last post: by
26 posts views Thread by Bail | last post: by
28 posts views Thread by Nutkin | last post: by
5 posts views Thread by thehonos | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.