473,226 Members | 1,515 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 1795
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

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

Similar topics

7
by: Muzamil | last post by:
Hi I want a simple select query on a column-name (smalldatetime) with values dislayed in desc order with null values FIRST. i.e. Select orderdate from orders order by ( null values first...
13
by: Joe Black | last post by:
Just to inform you guys that i have only like 2 weeks that i took my first classes in c++, and my proffesor now is asking me to solve this problem: /// Using a function create a Win32 Console...
3
by: Paul T. Rong | last post by:
Hello there, I am making a report using codes bellow (a part of): ------------------------------- Select Case Forms!!TopBoard Dim strGetSQL As String
3
by: John | last post by:
Hi there - if anyone has a few spare minutes I could really do with some help please. I'm a student and have been given an exercise using MS Access to design a simple database for a fictional...
26
by: Bail | last post by:
I will have a exam on the oncoming friday, my professor told us that it will base upon this program. i am having troubles understanding this program, for example what if i want to add all the...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
28
by: Nutkin | last post by:
Basicly i have to make a program that calculates the distance between x and y points in 2d space. the code basicly goes like this 1. User says how many points they have (max of 10) 2. User...
5
by: thehonos | last post by:
I am trying to help a former student. He needs to resolve this problem in javascript and I do not teach javacript and I am lost.. Can any one help?!?!?!? Write JavaScript to solve the following...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.