Home » SQL & PL/SQL » SQL & PL/SQL » Could you, please, explain this query? (merged & renamed by LF)
Could you, please, explain this query? (merged & renamed by LF) [message #261939] Fri, 24 August 2007 01:59 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
hello ALL, how are you,
i am using a query in generalized "pivot" procedure,
i use this sub-query in from clause,
the query is
select deptno, job, ename, sal,
       row_number() over ( partition by deptno, job
                           order  by deptno , job ) rn
  from emp 

and then i do the pivoting on specified column using rn,
will you explain please,
why is it better to use this query rather than
'emp' itself in where clause ,
why we perform pivoting on rn , as we do have pivot column in hand.
-- please explain the query even if u r into pivoting or not.
regards and thanx

PEACE
explain query [message #261943 is a reply to message #261939] Fri, 24 August 2007 02:22 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
select deptno, job, ename, sal,
                             row_number() over ( partition by deptno, job
                                                 order by sal, ename ) rn
                        from emp a',

please any one explain this query in detail ,
Re: explain query [message #261945 is a reply to message #261943] Fri, 24 August 2007 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno, job, ename, sal,
  2                               row_number() over ( partition by deptno, job
  3                                                   order by sal, ename ) rn
  4                          from emp a',
  5  /
ERROR:
ORA-01756: quoted string not properly terminated

It is not a valid SQL statement.

Regards
Michel
Re: explain query [message #261949 is a reply to message #261945] Fri, 24 August 2007 02:38 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
select deptno, job, ename, sal,
                             row_number() over ( partition by deptno, job
                                                 order by sal, ename ) rn
                        from emp

no its workin alright ,
but i want to know how oracle server parse it ,
and what advantage of partitioning do we have in this query
THANX AND REGARDS

PEACE
Re: explain query [message #261951 is a reply to message #261949] Fri, 24 August 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what advantage of partitioning do we have in this query

None unless you want this information.
Did you have a look at its output?
Did read something about analytic function and the meaning of the syntax?

Regards
Michel
Re: Could you, please, explain this query? (merged & renamed by LF) [message #262278 is a reply to message #261939] Sat, 25 August 2007 19:12 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
I don't mean to get personal, but why are you always so cranky Michel? You are obviously a tremendous resource, and it is great that you want to share your knowledge with other less experienced professionals, but lighten up a little...sure the documentation explains the function, but when you're a novice it can be difficult to know where to look and how to interpret what you're reading.

I apologize for my little rant, now on to the explanation of the query:

First some information about the use of ROW_NUMBER.

ROW_NUMBER is an analytic function. You can think of an analytic function as a means of summarizing data. This type of function, which is identified with the OVER keyword, is very useful when you want to compare row level data to summary data. In your example, ROW_NUMBER is used as a ranking function to identify which EMP row has the smallest SAL (further sorted by ENAME if there is a tie). The PARTITION BY clause is similar to a GROUP BY clause. The rank will be established within each unique combination of DEPTNO and JOB.

For example, you'll notice in the results that there are 4 employees in DEPTNO 30 with the JOB of 'SALESMAN'. These 4 rows will be grouped and ranked together. Of these 4 rows, 'MARTIN' and 'WARD' have the lowest SAL. Since the ORDER BY subclause further sorts by ENAME, the RN (or ROW_NUMBER) for 'MARTIN' is 1 and 'WARD' is 2. 'ALLEN' has the highest SAL in the group; therefore the RN is 4.
    DEPTNO JOB       ENAME             SAL         RN
---------- --------- ---------- ---------- ----------
        10 CLERK     MILLER           1300          1
        10 MANAGER   CLARK            2450          1
        10 PRESIDENT KING             5000          1
        20 ANALYST   SCOTT            3000          1
        20 ANALYST   FORD             3000          2
        20 CLERK     SMITH             800          1
        20 CLERK     ADAMS            1100          2
        20 MANAGER   JONES            2975          1
        30 CLERK     JAMES             950          1
        30 MANAGER   BLAKE            2850          1
        30 SALESMAN  MARTIN           1250          1
        30 SALESMAN  WARD             1250          2
        30 SALESMAN  TURNER           1500          3
        30 SALESMAN  ALLEN            1600          4


As for the performance of the query, it is difficult to say why this query is better than using the EMP table itself. Please supply the details of the entire query.

I would guess your main query needs to know the ROW_NUMBER, which must be calculated first. Analytic functions cannot be used directly in a WHERE clause; therefore a common solution is to calculate the ROW_NUMBER in a subquery in the FROM clause and reference it in the main query.

In general, analytic functions require intermediate sorts, which can be an issue with performance on large data sets so analytic functions should only be used when necessary.

I hope all that makes sense. I know I'm going to catch a lot of grief for my initial comments, so let me have it Wink
Re: Could you, please, explain this query? (merged & renamed by LF) [message #262301 is a reply to message #262278] Sun, 26 August 2007 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't mean to get personal, but why are you always so cranky Michel?

Quote:
I know I'm going to catch a lot of grief for my initial comments

No grief, just a little explaination and you will immediatly understand.

First, you explain what is analytic function, this is useless if OP read the documentation. You said maybe he don't where to search, this is explained in the sticky. Every one that wants help should read them. Is this too much asked? Especially for someone you already posted 73 messages.

Then, I quote you:
Quote:
Please supply the details of the entire query.

Quote:
I would guess your main query needs...

This why I ask for posters to be more precise, to detail the question and problem. It prevent us from posting useless things and it learns OP how to think and describe their problem and I think this is more important than the solution.
See the question, a query (not even a valid one) and "explain!". Is this a professional way to ask?

Regards
Michel
Re: Could you, please, explain this query? (merged & renamed by LF) [message #262335 is a reply to message #262301] Sun, 26 August 2007 04:23 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
and it learns OP how to think and describe their problem and I think this is more important than the solution.


I think that this is the crux of the matter. Too many people are far too lazy to do just a small amount of research into their problem. For the most part, if they did some reading on the basic documentation, or googled their problem they would fnd clear and easy solutions. If they cannot find the solution, all that is asked is that people put forward a coherent question with all pertinant details so that people do not waste their time toing and fro-ing with questions and guesses, trying to ascertain the REAL issue behind the question.

Give a man a fish and he'll eat for a day, teach a man to fish and he'll eat for the rest of is life (although I prefer .. Teach a man to fish and he'll sit in a boat all day drinking beer Smile )
Re: Could you, please, explain this query? (merged & renamed by LF) [message #262793 is a reply to message #262335] Tue, 28 August 2007 05:16 Go to previous message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
thanx a lot Cmerry for your kind explanation of the query in such detail , and really i know very few people take the pains of explaining things in such a detail.
and michel i have always been your admirer for your skills,
.and why i asked about that query well let me tell you , i created a procedure generalized for pivting ,
i had a problem in its decode( to convert select column into rows) some advised me to use row partitioning ,
thats why i asked about the query.
thanx Cmerry , not only u are very good at oracle , but you are very nice hearted as well.
thanx both of you
Cmerry and Michel

regards

PEACE
Previous Topic: Sql query
Next Topic: Problem with a bracket
Goto Forum:
  


Current Time: Wed Dec 07 22:19:02 CST 2016

Total time taken to generate the page: 0.05335 seconds