SQL
From SkyWiki
(Difference between revisions)
(→TOP SELECT) |
|||
| Line 25: | Line 25: | ||
MySQL: | MySQL: | ||
<source lang=sql> | <source lang=sql> | ||
| - | + | select EMPNO, ENAME, JOB, MGR, SQL | |
| - | + | from EMP | |
| - | + | limit 10 offset 20 | |
</source> | </source> | ||
| Line 41: | Line 41: | ||
from NM_SCHD_JOB_RUN | from NM_SCHD_JOB_RUN | ||
) | ) | ||
| - | where rn | + | 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; | ||
</source> | </source> | ||
MS SQL Server: | MS SQL Server: | ||
| - | |||
<source lang=sql> | <source lang=sql> | ||
| - | + | select top 10 EMPNO, ENAME, JOB, MGR | |
| - | + | from EMP | |
</source> | </source> | ||
Revision as of 06:55, 16 September 2008
traditional Oracle outer join syntax
left outer join
right outer join
inner join
TOP SELECT
MySQL:
SELECT EMPNO, ENAME, JOB, MGR, SQL FROM EMP LIMIT 10 offset 20
Oracle >= 8.1.6:
SELECT * FROM (SELECT JOB_NUM, JOB_NME, START_DT_GMT, STATUS_CD, rank() over(partition BY JOB_NUM ORDER BY START_DT_GMT DESC ) rn FROM NM_SCHD_JOB_RUN ) 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
