8 Replies Latest reply on Apr 4, 2017 10:07 PM by RAMMII

    Oracle SQL - How to limit rows and include the tied columns in the result

    RAMMII

      Hi ... I have one issue with particular  PL SQL query (ordering specific column ), i am trying to limit the number of rows in the query but i wanted to include the tied rows also of that ordering column as well.

       

      I think in 12c we have some function like FETCH FIRST N WITH TIED ROWS  but i have older version of Oracle.

       

      Could you please let me know how can I handle this scenario, is it possible with joins or something.

       

      EXAMPLE :

       

      CLM1  CLM2

        A1        1

        A2        1

        A3        1

        A3        2

        A4        1

        A5        1

      I want to get the first 3 records of CLM1 but when it matches the next rows i should fetch those also in my result.

       

      sample query i am using but it's not suit for my requirement.

       

      select * from T  where  rownum <4 order by CLM1

       

      Please help me with this query.

        • 1. Re: Oracle SQL - How to limit rows and include the tied columns in the result
          jaramill

          Read the thread on --> Re: 2. How do I ask a question on the forums?

           

          And then provide DDL and DML statements.

          We have NO clue what table "T" is defined as?

          What are the columns?

          What is the data?

          • 2. Re: Oracle SQL - How to limit rows and include the tied columns in the result
            Andrew Sayer

            RAMMII wrote:

             

            Hi ... I have one issue with particular PL SQL query (ordering specific column ), i am trying to limit the number of rows in the query but i wanted to include the tied rows also of that ordering column as well.

             

            I think in 12c we have some function like FETCH FIRST N WITH TIED ROWS but i have older version of Oracle.

             

            Could you please let me know how can I handle this scenario, is it possible with joins or something.

             

            EXAMPLE :

             

            CLM1 CLM2

            A1 1

            A2 1

            A3 1

            A3 2

            A4 1

            A5 1

            I want to get the first 3 records of CLM1 but when it matches the next rows i should fetch those also in my result.

             

            sample query i am using but it's not suit for my requirement.

             

            select * from T where rownum <4 order by CLM1

             

            Please help me with this query.

            Use the rank or dense_rank analytic function, perhaps

             

            select cols from (select cols, dense_rank() over (partition by null order by clm1) rnk from t) where rnk <4

             

            BTW rownum <4 order by clm4 is almost certainly a bug, it'll will apply the order by AFTER it filters on the rownum

            • 3. Re: Oracle SQL - How to limit rows and include the tied columns in the result
              mathguy

              This is exactly what the RANK() function is for. It works for the entire table, or it also works for "groups" of rows that should be analyzed independently of each other. (If the entire table must be one big "group", then the PARTITION BY clause can be omitted.)

               

              I illustrate this with the EMP table in the SCOTT schema. There are 14 rows in the EMP schema. EMPNO is the primary key, and there are columns SAL for salary and DEPTNO for department number. There are three departments: 10 (with three employees), 20 (five employees), 30 (six employees). In the query below I group the employees by department, order them by salary descending, and attach a RANK() to each row within each department. Note how the RANK() function works: if two (or more) rows are tied by SAL, they get the same RANK. The next RANK does not continue in sequence; rather, if four rows have RANK() = 8 (say, in a larger table than this one), then that "uses up" ranks 8, 9, 10, 11; so the next assigned rank, after rank 8 appearing four times, is rank 12. This is seen, in a much smaller example, on the EMP table below.


              Then suppose you want the four highest salaries in each department (and the employees who receive them) - but if two or more are tied at the bottom of the list, you want all the "ties" to be included. This is done very easily by using the query below as a SUBQUERY (which can't be avoided in Oracle 11 or lower); and in the other query just filter by RNK <= 4. I don't run that query; instead, I highlight in red the rows that would be returned. Of course, if a department has four or fewer employees, they will all be selected. (Dept. 10 is in that situation.)

               

              Note that there is also a function DENSE_RANK(), which works differently. The best way to see how is to re-run the query below, with DENSE_RANK() instead of RANK(). This will show also why that function is NOT the correct answer for this type of problem.

               

              select   deptno, empno, sal,
                       rank() over (partition by deptno order by sal desc) rnk
              from     emp
              order by deptno, rnk, empno
              ;

               

                  DEPTNO  EMPNO   SAL  RNK
                --------  -----  ----  ---
                      10   7839  5000    1
                      10   7782  2450    2
                      10   7934  1300    3

                                                                               -- blank line not in the output, I only added it in this post for clarity
                      20   7788  3000    1
                      20   7902  3000    1
                      20   7566  2975    3
                      20   7876  1100    4
                      20   7369   800    5

               

                      30   7698  2850    1
                      30   7499  1600    2
                      30   7844  1500    3
                      30   7521  1250    4
                      30   7654  1250    4
                      30   7900   950    6

              • 4. Re: Oracle SQL - How to limit rows and include the tied columns in the result
                RAMMII

                Thanks for the response mathguy.

                 

                From your example if i want to limit the total output records, Example if i want only 6 records,  so i have to get 6 records but along with that the same TIED DEPT number records as well, that means  i have to get complete 8 records (first two blocks from your table).

                 

                Basically i want to group by DEPTNO and approximately limit the number of records.

                 

                How can i achieve that result?

                • 5. Re: Oracle SQL - How to limit rows and include the tied columns in the result
                  RAMMII

                  Thanks for the response Andrew sayer.

                  • 6. Re: Oracle SQL - How to limit rows and include the tied columns in the result
                    Frank Kulash

                    Hi,

                    RAMMII wrote:

                     

                    Thanks for the response mathguy.

                     

                    From your example if i want to limit the total output records, Example if i want only 6 records, so i have to get 6 records but along with that the same TIED DEPT number records as well, that means i have to get complete 8 records (first two blocks from your table).

                     

                    Basically i want to group by DEPTNO and approximately limit the number of records.

                     

                    How can i achieve that result?

                    Describe, in general, what you want.

                     

                    In reply #3, Mathguy showed you how to find the Top N rows (including ties) in DESCeding order of sal, with a separate set of results for each deptno:

                     

                    rank() over (partition by deptno order by sal desc)

                     

                    "PARTITION BY deptno" means do a separate calculation for each value of deptno.

                    "ORDER BY sal DESC" means the highest sal for each deptno will be assigned the value 1.

                     

                    If you want to find the top 6 rows (including ties) in ascending order of deptno, with just one set of numbers for the whole result set, then omit the PARTITION BY clause, and change the analytic ORDER BY clause to "ORDER BY deptno"., like this:

                    WITH    got_rnk    AS

                    (

                        SELECT   deptno, empno, sal   -- Or whatever columns you want

                        ,        RANK () OVER (ORDER BY deptno) rnk

                        FROM     scott.emp

                    )

                    SELECT    deptno, empno, sal

                    FROM      got_rnk

                    WHERE     rnk  <= 6

                    ORDER BY  deptno, rnk, empno

                    ;

                    Output:

                      DEPTNO    EMPNO      SAL

                    -------- -------- --------

                          10     7782     2450

                          10     7839     5000

                          10     7934     1300

                          20     7369      800

                          20     7566     2975

                          20     7788     3000

                          20     7876     1100

                          20     7902     3000

                     

                    8 rows selected.

                    • 7. Re: Oracle SQL - How to limit rows and include the tied columns in the result
                      Barbara Boehmer

                      SCOTT@orcl_12.1.0.2.0> -- version:

                      SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version

                        2  /

                       

                      BANNER

                      --------------------------------------------------------------------------------

                      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                      PL/SQL Release 12.1.0.2.0 - Production

                      CORE    12.1.0.2.0    Production

                      TNS for 64-bit Windows: Version 12.1.0.2.0 - Production

                      NLSRTL Version 12.1.0.2.0 - Production

                       

                      5 rows selected.

                       

                      SCOTT@orcl_12.1.0.2.0> -- test data:

                      SCOTT@orcl_12.1.0.2.0> SELECT * FROM t ORDER BY clm1, clm2

                        2  /

                       

                      CL      CLM2

                      -- ----------

                      A1          1

                      A2          1

                      A3          1

                      A3          2

                      A4          1

                      A5          1

                       

                      6 rows selected.

                       

                      SCOTT@orcl_12.1.0.2.0> -- query for newer versions:

                      SCOTT@orcl_12.1.0.2.0> SELECT * FROM t ORDER BY clm1 FETCH FIRST 3 ROWS WITH TIES

                        2  /

                       

                      CL      CLM2

                      -- ----------

                      A1          1

                      A2          1

                      A3          1

                      A3          2

                       

                      4 rows selected.

                       

                      SCOTT@orcl_12.1.0.2.0> -- query for older versions:

                      SCOTT@orcl_12.1.0.2.0> SELECT clm1, clm2

                        2  FROM  (SELECT clm1, clm2, RANK() OVER (ORDER BY clm1) rk

                        3          FROM  t)

                        4  WHERE  rk <= 3

                        5  ORDER  BY clm1, clm2

                        6  /

                       

                      CL      CLM2

                      -- ----------

                      A1          1

                      A2          1

                      A3          1

                      A3          2

                       

                      4 rows selected.

                      1 person found this helpful
                      • 8. Re: Oracle SQL - How to limit rows and include the tied columns in the result
                        RAMMII

                        Thanks Barbara. Barbara Boehmer

                         

                        It worked perfectly as per requirement.