SQL

From SkyWiki

(Difference between revisions)
Jump to: navigation, search
m (TOP SELECT)
m (TOP n SELECT)
Line 33: Line 33:
<source lang=sql>
<source lang=sql>
select * from (select  
select * from (select  
-
JOB_NUM,  
+
    EMPNO, ENAME, JOB, MGR, SQL,
-
JOB_NME,  
+
    rank() over(partition by JOB order by HIRE_DATE desc  
-
START_DT_GMT,  
+
) RN
-
STATUS_CD,  
+
from EMP
-
rank() over(partition by JOB_NUM order by START_DT_GMT desc  
+
-
) rn
+
-
from NM_SCHD_JOB_RUN
+
)  
)  
-
where rn between 20 and 30
+
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
Personal tools