SQL
From SkyWiki
(Difference between revisions)
m (→TOP SELECT) |
m (→TOP n SELECT) |
||
| Line 33: | Line 33: | ||
<source lang=sql> | <source lang=sql> | ||
select * from (select | select * from (select | ||
| - | + | EMPNO, ENAME, JOB, MGR, SQL, | |
| - | + | rank() over(partition by JOB order by HIRE_DATE desc | |
| - | + | ) RN | |
| - | + | from EMP | |
| - | rank() over(partition by | + | |
| - | ) | + | |
| - | from | + | |
) | ) | ||
| - | where | + | where RN between 20 and 30 |
select EMPNO, ENAME, JOB, MGR, SAL, | select EMPNO, ENAME, JOB, MGR, SAL, | ||
Revision as of 07:01, 16 September 2008
traditional Oracle outer join syntax
left outer join
right outer join
inner join
TOP n SELECT
MySQL:
SELECT EMPNO, ENAME, JOB, MGR, SQL FROM EMP LIMIT 10 offset 20
Oracle >= 8.1.6:
SELECT * FROM (SELECT EMPNO, ENAME, JOB, MGR, SQL, rank() over(partition BY JOB ORDER BY HIRE_DATE DESC ) RN FROM EMP ) WHERE RN BETWEEN 20 AND 30 SELECT EMPNO, ENAME, JOB, MGR, SAL, rank() over (ORDER BY SAL DESC NULLS LAST) AS RANK, dense_rank() over (ORDER BY SAL DESC NULLS LAST) AS DRANK FROM EMP ORDER BY SAL DESC NULLS last;
MS SQL Server:
SELECT top 10 EMPNO, ENAME, JOB, MGR FROM EMP
Sybase:
SET rowcount 10 SELECT COLUMNS FROM TABLE
Firebird:
SELECT first 10 COLUMNS FROM TABLE
