| problem in choosing top rows using group by function [message #11224] |
Mon, 15 March 2004 00:06  |
ARANGASWAMY.V.
Messages: 29
Registered: April 2002
|
Junior Member |
|
|
Sir,
I wanted to select the top 20 companies who pay tax more with their Id_no,name and tax (on which group function "SUM" has to be applied).
I framed the query as given in the Main site under the topic Server Utilities / SQL /"How does one select top N rows from a table".
SELECT id_no, name, tax
FROM (SELECT id_no,name,SUM(tax) TAX FROM my_table ORDER BY col_name_3 DESC)
WHERE ROWNUM < 21;
But the query acts funny.
I wanted to know whether the query would function well when we combine group by functions / rownum etc. in the select statement
I also wanted to know whether order by clause and pseducolumn rownum could be used together in the main select statement.
Thanks in advance,
Arangu
|
|
|
|
| Re: problem in choosing top rows using group by function [message #11233 is a reply to message #11224] |
Mon, 15 March 2004 02:04  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Below is the proper syntax for your query:
SELECT id_no, name, sum_tax AS tax
FROM (SELECT id_no, name, SUM (tax) AS sum_tax
FROM my_table
GROUP BY id_no, name
ORDER BY sum_tax DESC)
WHERE ROWNUM < 21;
You left out the group by in the query that you posted. You need to order by the column (sum_tax) that you use to determine which rows to select. This ordering must be done within the inner sub-query and the rows must be selected useing rownum from the outer query. This first puts the rows in order, then numbers them. You cannot use both order by and rownum within just one query and get the correct results. If they are both within only one query the row numbering is done first, then the ordering, so you would get the rows in whatever order they happened to be retrieved in, prior to ordering, and you could not then use rownum to retrieve the correct ones.
If this does not answer your questions, then please provide more specifics, such as a cut and paste of the query that you run, the results that you get, the results that you want, and an explanation of what "acts funny" means.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
