CODEWITHSUNDEEP
oracle-databaseselectoracle12c
Hossein
Hossein

Reputation: 26004

How to fetch top ten rows of different tables in oracle?

I have 4 tables which have 5 columns in common. I want to fetch the latest overall top ten rows for these tables, and retrieve them.
How can i do that? I tried using a simple join command, but instead of 5 columns I get 20 columns! each table has its own column name alias! but what I want is, to have a 5 column table which contains all of those top 10s.

Update:

fetch FIRST 10 ROWS ONLY

Command returns the 10 first records, not the latest ones when I run the following command for example :

select * from tblTest order by ID FETCH First 3 ROWS Only;

I get 

1 Ali
2 Jacob
3 Chris

Where I actually needed 

18 Fatima
19 Mary
20 Catherina

How can i achieve such a thing?

Upvotes: 2

Views: 15672

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

In Oracle 12c, you can use the TOP-N query :

SQL> select * from(
  2  (select deptno from emp
  3  ORDER BY deptno
  4  fetch FIRST 10 ROWS ONLY)
  5  UNION all
  6  (select deptno from emp
  7  ORDER BY deptno
  8  fetch FIRST 10 ROWS ONLY)
  9  )
 10  /

    DEPTNO
----------
        10
        10
        10
        20
        20
        20
        20
        20
        30
        30
        10

    DEPTNO
----------
        10
        10
        20
        20
        20
        20
        20
        30
        30

20 rows selected.

Unlike ROWNUM, you need not worry about the ORDER.

Update Regarding last records in the order

Use DESC in order.

SQL> SELECT *
  2  FROM(
  3    (SELECT deptno FROM emp ORDER BY deptno DESC
  4    FETCH FIRST 3 ROWS ONLY
  5    )
  6  UNION ALL
  7    (SELECT deptno FROM emp ORDER BY deptno DESC
  8    FETCH FIRST 3 ROWS ONLY
  9    ) )
 10  /

    DEPTNO
----------
        30
        30
        30
        30
        30
        30

6 rows selected.

SQL>

Upvotes: 3

Mureinik
Mureinik

Reputation: 311843

You can use union all to get all the rows consecutively, and then take the top 10:

SELECT col1, col2, col3, col4, col5
FROM   (SELECT col1, col2, col3, col4, col5
        FROM    (SELECT col1, col2, col3, col4, col5 FROM table1
                 UNION ALL
                 SELECT col1, col2, col3, col4, col5 FROM table2
                 UNION ALL
                 SELECT col1, col2, col3, col4, col5 FROM table3
                 UNION ALL
                 SELECT col1, col2, col3, col4, col5 FROM table4)
        ORDER BY col1, col2, col3, col4, col5)
WHERE   rownum <= 10

Upvotes: 3

Related Questions