http://www.dba.5341.com/
http://www.dba.5341.com/
Hi Muhammed
It’s just a case study. Is it
possible to do without the sub query?
Actually, the original question is not from
me, but from a friend of mine. I was just thinking about a good solution all
morning long and couldn’t figure out a good answer to the question.
I find it quite reasonable to try a simple
thing like a Top-N analysis without the use of a (possibly costly) sub query.
But maybe I’m just spoiled by the
proprietary MySQL feature of LIMIT BY…
Regards,
Tristan
------------------------------------------------------------------------------------
Tristan van Essen
Accenture Technology
Solutions AG
Baslerstrasse 60
CH-8048 Zürich
Office: +41 44 405 3245
Email: tristan.van.essen@accenture.com
From: Muhammed Soyer
[mailto:msoyer@gmail.com]
Sent: Mittwoch, 7. Juni 2006 12:02
To: van Essen, Tristan
Cc: oracle-l@freelists.org
Subject: Re: How to do a Top-N
analysis, while avoiding subqueries
Why dont you want to use
sub queries ? Any solid reason or just for curiosity ..
2006/6/7, tristan.van.essen@accenture.com
<tristan.van.essen@accenture.com>:
Hi
folks, this is my first question in this mailing list.
How
do you do a top-n analysis while avoiding subqueries, but still use group
functions on other columns?
I
have following Query:
SELECT COUNT(city)
quantity, city
FROM customer
GROUP BY city
ORDER
BY quantity DESC;
This
works fine so far with the exception that I only want the first record
returned, instead of all. I tried to solve the problem with the ROWNUM
function, but it won't work well. The problem here is that I must include
ROWNUM in the GROUP BY clause, else the query fails. But then the result isn't
right anymore. I wish there was any sort of LIMIT BY clause, like there is in
MySQL.
How
can I solve this, while not using any subqueries?
Regards,
Tristan
------------------------------------------------------------------------------------
Tristan van Essen
Accenture Technology Solutions AG
Baslerstrasse 60
CH-8048 Zürich
Office: +41 44 405 3245
Email: tristan.van.essen@accenture.com
This
message is for the designated recipient only and may contain privileged,
proprietary, or otherwise private information. If you have received it in
error, please notify the sender immediately and delete the original. Any other
use of the email by you is prohibited.
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.