I have written the following query:
SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL
FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL
FROM PURCHASE GROUP BY CLIENTNO) TOTALS
WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO AND ROWNUM <= 1
ORDER BY TOTALS.TOTAL DESC;
However it is giving me the wrong answer, but if I remove the ROWNUM <= 1
clause, then the correct answer is at the top of the result set.
So what can I change in this query to make it produce the correct answer?
Thanks, Alex.
EDIT: Forgot to say that I only want the query to return the the first result in the result set.
To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement.
Re: why does "select 1 from dual" work - what is the "1"? Dual is table that has 1 column and 1 row. 1 is a 'literal' that will be returned by Oracle as it is from the Database. Basically, we use the Dual table to get something/anything from Oracle database once.
There is no difference between EXISTS with SELECT * and SELECT 1. SQL Server generates similar execution plans in both scenarios. EXISTS returns true if the subquery returns one or more records. Even if it returns NULL or 1/0.
The ROWNUM
filter applies before the sorting. What you need to do is this:
SELECT * FROM (
SELECT CLIENT.CLIENTNO, CLIENT.CNAME, TOTALS.TOTAL
FROM CLIENT, (SELECT CLIENTNO, SUM(AMOUNT) AS TOTAL
FROM PURCHASE GROUP BY CLIENTNO) TOTALS
WHERE CLIENT.CLIENTNO = TOTALS.CLIENTNO
ORDER BY TOTALS.TOTAL DESC
)
WHERE ROWNUM <= 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With