Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: limit number of results query

    I'm new to oracle and having an issue returning the first four results from a query involving two table joins.

    I have a person table and an order table. I've been give the task of selecting the first four orders for each person. Can someone help me out with this?

    I understand that I can use rownum to limit the number of rows but how do I select the first 4 orders and would I have to group by person?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Can you compose SQL when you do not know table or column names?
    I can't do so
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example based on Scott's schema, its EMP and DEPT tables. As there are not many records in those tables, let's imagine that we want to select the first ("richest", with highest salaries) two employees per each department, sorted by their salaries.

    For the beginning, let's see what we have:
    Code:
    SQL> select
      2    d.deptno,
      3    d.dname,
      4    e.ename,
      5    e.sal
      6  from dept d,
      7       emp e
      8  where d.deptno = e.deptno
      9  order by d.deptno, e.sal desc;
    
        DEPTNO DNAME          ENAME             SAL
    ---------- -------------- ---------- ----------
            10 ACCOUNTING     KING             5000
            10 ACCOUNTING     CLARK            2450
            10 ACCOUNTING     MILLER           1300
            20 RESEARCH       SCOTT            3000
            20 RESEARCH       FORD             3000
            20 RESEARCH       JONES            2975
            20 RESEARCH       ADAMS            1100
            20 RESEARCH       SMITH             800
            30 SALES          BLAKE            2850
            30 SALES          ALLEN            1600
            30 SALES          TURNER           1500
            30 SALES          MARTIN           1250
            30 SALES          WARD             1250
            30 SALES          JAMES             950
    Obviously, we need:
    Code:
    10: King, Clark
    20: Scott, Ford (and, possibly, Jones because Scott and Ford have the same salaries)
    30: Blake, Allen
    So we'll have to sort those people. In order to do that, we'll use analytic functions. RANK (or, possibly, DENSE_RANK, depending on what you want to do with Scott/Ford/Jones situation):
    Code:
    SQL> select
      2    rank() over (partition by d.deptno order by e.sal desc) rn_rank,
      3    dense_rank() over (partition by d.deptno order by e.sal desc) rn_dense_rank,
      4    d.deptno,
      5    d.dname,
      6    e.ename,
      7    e.sal
      8  from emp e,
      9       dept d
     10  where d.deptno = e.deptno;
    
       RN_RANK RN_DENSE_RANK     DEPTNO DNAME          ENAME             SAL
    ---------- ------------- ---------- -------------- ---------- ----------
             1             1         10 ACCOUNTING     KING             5000
             2             2         10 ACCOUNTING     CLARK            2450
             3             3         10 ACCOUNTING     MILLER           1300
             1             1         20 RESEARCH       SCOTT            3000
             1             1         20 RESEARCH       FORD             3000
             3             2         20 RESEARCH       JONES            2975
             4             3         20 RESEARCH       ADAMS            1100
             5             4         20 RESEARCH       SMITH             800
             1             1         30 SALES          BLAKE            2850
             2             2         30 SALES          ALLEN            1600
             3             3         30 SALES          TURNER           1500
             4             4         30 SALES          MARTIN           1250
             4             4         30 SALES          WARD             1250
             6             5         30 SALES          JAMES             950
    See the difference in Jones' row? His RN is 2 or 3, depending on what you need. Let's assume that we really want to see him in our result set (so we'll use DENSE_RANK).

    Therefore, we'll use the above query as an inline view:
    Code:
    SQL> select
      2    x.rn,
      3    x.deptno,
      4    x.dname,
      5    x.ename,
      6    x.sal
      7  from (select
      8          dense_rank() over (partition by d.deptno order by e.sal desc) rn,
      9          d.deptno,
     10          d.dname,
     11          e.ename,
     12          e.sal
     13        from emp e,
     14             dept d
     15        where d.deptno = e.deptno
     16       ) x
     17  where x.rn <= 2;
    
            RN     DEPTNO DNAME          ENAME             SAL
    ---------- ---------- -------------- ---------- ----------
             1         10 ACCOUNTING     KING             5000
             2         10 ACCOUNTING     CLARK            2450
             1         20 RESEARCH       SCOTT            3000
             1         20 RESEARCH       FORD             3000
             2         20 RESEARCH       JONES            2975
             1         30 SALES          BLAKE            2850
             2         30 SALES          ALLEN            1600
    That would be all, I suppose. Try to apply such an approach to your case and see what happens.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •