ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Top-N SQL queries with Inline Views[akadia]

Top-N SQL queries with Inline Views[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-01-22 12:45:06 0 删除 编辑

Top-N queries

Suppose you want to retrieve and sort information on the 4 most recently hired employees in a very efficient way. This can be achieved using an inline view combined with ORDER BY and ROWNUM.

Inline Views

An in-line view, which is a feature of a Top-N SQL query, is a subquery. This type of subquery differs from a regular subquery by containing an ORDER BY clause which is not allowed in a regular subquery. The ROWNUM condition, which is an enhanced sorting mechanism, would be used in the outer query to complete the Top-N SQL query.

Example

We need to retrieve and sort information on the 4 most recently hired employees from the following list (marked in blue color).

SELECT empno,ename,TO_CHAR(hiredate,'DD.MM.YYYY') "hiredate"
FROM emp
ORDER BY hiredate DESC;

EMPNO ENAME hiredate
---------- ---------- ----------
7876 ADAMS 12.01.1983
7788 SCOTT 09.12.1982
7934 MILLER 23.01.1982
7900 JAMES 03.12.1981
7902 FORD 03.12.1981
7839 KING 17.11.1981
7654 MARTIN 28.09.1981
7844 TURNER 08.09.1981
7782 CLARK 09.06.1981
7698 BLAKE 01.05.1981
7566 JONES 02.04.1981
7521 WARD 22.02.1981
7499 ALLEN 20.02.1981
7369 SMITH 17.12.1980

The first approach is to used the following query, which does not select the Top-4 rows !

SELECT empno,ename,hiredate
FROM emp
WHERE ROWNUM < 5
ORDER BY hiredate DESC;

EMPNO ENAME HIREDATE
---------- ---------- ----------
7566 JONES 02.04.1981
7521 WARD 22.02.1981
7499 ALLEN 20.02.1981
7369 SMITH 17.12.1980

The solution is to use an inline view with an ORDER BY and a ROWNUM condition, in the outer query to complete the Top-N SQL query.

SELECT *
FROM (SELECT empno,ename,hiredate
FROM emp
ORDER BY hiredate DESC)

WHERE ROWNUM < 5;

EMPNO ENAME HIREDATE
---------- ---------- ----------
7876 ADAMS 12.01.1983
7788 SCOTT 09.12.1982
7934 MILLER 23.01.1982
7900 JAMES 03.12.1981

The purpose of this query is to retrieve and sort information on the 4 most recently hired employees. This is a Top-N SQL query which is more efficient than a regular query because Oracle stores a maximum of only 5 rows as the data is retrieved from the table avoiding sorting all of the rows in the table at once. The WHERE clause contains 'ROWNUM < 5' which prevents sorting on more than 5 rows at one time -- cool isn't it ?


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60586/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    110
  • 访问量
    82294