SQL

From SkyWiki

(Difference between revisions)
Jump to: navigation, search
(TOP SELECT)
Line 25: Line 25:
MySQL:
MySQL:
<source lang=sql>
<source lang=sql>
-
SELECT product, descr, email
+
select EMPNO, ENAME, JOB, MGR, SQL
-
FROM products
+
from  EMP
-
LIMIT 10
+
limit 10 offset 20
</source>
</source>
Line 41: Line 41:
from NM_SCHD_JOB_RUN  
from NM_SCHD_JOB_RUN  
)  
)  
-
where rn < 10
+
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:
-
MySQL:
 
<source lang=sql>
<source lang=sql>
-
SELECT TOP 10 product, descr, email
+
select top 10 EMPNO, ENAME, JOB, MGR
-
FROM products
+
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
Personal tools