OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » rownum query (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

rownum query [message #631432]

Wed, 14 January 2015 01:15

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

HI ALL,

I WANT TO FETCH 10 ROWS BUT GIVING PRIORITY TO ALL THE JOB.
PLESE HELP ON THIS.

SELECT * FROM (SELECT E.*,ROW_NUMBER() OVER  (PARTITION BY JOB ORDER BY ENAME )RN FROM EMP E)
WHERE ROWNUM<=10 AND RN <= 2 ;

REGARDS,
Nathan

[Updated on: Wed, 14 January 2015 01:16]

Report message to a moderator

Re: rownum query [message #631434 is a reply to message #631432]

Wed, 14 January 2015 01:19

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

SQL> SELECT * 
  2  FROM   (SELECT E.*, 
  3                 Row_number() 
  4                   over ( 
  5                     PARTITION BY job 
  6                     ORDER BY ename )RN 
  7          FROM   emp E) 
  8  WHERE  ROWNUM <= 10 
  9  --AND RN <= 2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3300                    20          1
      7788 SCOTT      ANALYST         7566 09-DEC-82       3300       7934         20          2
      7876 ADAMS      CLERK           7788 12-JAN-83       2393       7934         20          1
      7900 JAMES      CLERK           7698 03-DEC-81       4180        380         30          2
      7934 MILLER     CLERK           7782 23-JAN-82       3575       7934         10          3
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20          4
      7369 SMITH      CLERK           7902 17-DEC-80       2632       7934         20          5
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          1
      7698 BLAKE      MANAGER         7839 01-MAY-81       3135                    30          2
      7782 CLARK      MANAGER         7839 09-JUN-81       3575       7934         10          3

10 rows selected.

Report message to a moderator

Re: rownum query [message #631435 is a reply to message #631434]

Wed, 14 January 2015 01:21

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Thank you Mist598 but I need in that 10 selected rows it must include all jobs also.

[Updated on: Wed, 14 January 2015 01:22]

Report message to a moderator

Re: rownum query [message #631437 is a reply to message #631435]

Wed, 14 January 2015 01:30

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

but I need in that 10 selected rows it must include all jobs also.

This??


  1  SELECT * FROM (SELECT E.*,dense_rank() OVER  (PARTITION BY deptno ORDER BY ENAME)RN FROM EMP E)
  2* WHERE ROWNUM<=10
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       3575       7934         10          1
      7839 KING       PRESIDENT            17-NOV-81       6600       7934         10          2
      7934 MILLER     CLERK           7782 23-JAN-82       3575       7934         10          3
      7876 ADAMS      CLERK           7788 12-JAN-83       2393       7934         20          1
      7902 FORD       ANALYST         7566 03-DEC-81       3300                    20          2
      7566 JONES      MANAGER         7839 02-APR-81       3273                    20          3
      7788 SCOTT      ANALYST         7566 09-DEC-82       3300       7934         20          4
      7369 SMITH      CLERK           7902 17-DEC-80       2632       7934         20          5
      7499 ALLEN      SALESMAN        7698 20-FEB-81       2246       7934         30          1
      7698 BLAKE      MANAGER         7839 01-MAY-81       3135                    30          2

10 rows selected.

Report message to a moderator

Re: rownum query [message #631438 is a reply to message #631432]

Wed, 14 January 2015 01:45

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

BUT GIVING PRIORITY TO ALL THE JOB.

What does this mean?
Post the result you should get.

And do NOT post in UPPER case. this is seen as shouting and why could you shout against us?

Report message to a moderator

Re: rownum query [message #631448 is a reply to message #631438]

Wed, 14 January 2015 02:04

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Sorry Michel,I never meant this.

Actually i want to pick 2 records(rn) from each job amongst 10 rows(rownum). If anything less rows if it fetches then it should pick extra rows to make it 10.

          first_time  second_time    total  
ANALYST   2                           2
CLERK     2           1               1
MANAGER   2                           2
PRESIDENT 1                           1
SALESMAN  2                           2 
total     9           1               10

[Updated on: Wed, 14 January 2015 02:05]

Report message to a moderator

Re: rownum query [message #631451 is a reply to message #631448]

Wed, 14 January 2015 02:09

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does "first time" and "second time" mean and come from?

Report message to a moderator

Re: rownum query [message #631452 is a reply to message #631448]

Wed, 14 January 2015 02:14

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this what you want (assuming you want to order each employee by its name in each job)?

SQL> with 
  2    data as (
  3      select e.*,
  4             row_number() over (partition by job order by ename) rn
  5      from emp e
  6      order by rn, job
  7    )
  8  select d.*
  9  from data d
 10  where rownum <= 10
 11  order by job, rn
 12  /
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20          1
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20          2
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20          1
      7900 JAMES      CLERK           7698 03-DEC-1981        950                    30          2
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10          3
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30          1
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10          2
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10          1
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30          1
      7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30          2

10 rows selected.

Report message to a moderator

Re: rownum query [message #631453 is a reply to message #631448]

Wed, 14 January 2015 02:16

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

sss111ind wrote on Wed, 14 January 2015 13:34

          first_time  second_time    total  
CLERK     2           1               1

Why is the total 1? Please show your input and expected output, and explain the rules to get the expected output.

Report message to a moderator

Re: rownum query [message #631455 is a reply to message #631453]

Wed, 14 January 2015 02:18

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I think it is a typo, it should 3, 2+1, and not 1, 2-1, otherwise the total is not 10!

Report message to a moderator

Re: rownum query [message #631456 is a reply to message #631455]

Wed, 14 January 2015 02:22

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Thank you very much Michel, before I clarified my question you have delivered the answer already. Yes that was a mistake.
My clarification as follows

In the first query two where clauses are there

1.Where rn<=2 (to pick 2 rows  of each job )
2.Where rownum<=10( to pick 10 rows)

But the above query gives 9 rows means ,1 extra should be picked in point1 to make it 10 in point 2.
And first_time and second_time is search operation of oracle not output of the query.

Regards,
Nathan

[Updated on: Wed, 14 January 2015 02:23]

Report message to a moderator

Previous Topic:	Table Access Full
Next Topic:	Handle duplicate Configurations (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 08:35:09 CDT 2024