Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select first result only

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.

like image 837
Alex Hope O'Connor Avatar asked Oct 20 '11 09:10

Alex Hope O'Connor


People also ask

How do I select the first 10 values in SQL?

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.

What is the use of select 1 from dual?

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.

What is the difference between select * and select 1?

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.


1 Answers

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 
like image 166
Lukas Eder Avatar answered Nov 15 '22 20:11

Lukas Eder