1.
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a < 5 --- say n is 5 display first 5 records
For last n rows:
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) xWHERE x.a < 5 FROM )WHERE .a<This query can be used for finding nth row also say n = 5
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a = 5
2.
T-SQL :
SELECT TOP n FROM Table_Name;
MySQL:
SELECT * FROM Table_Name LIMIT 0, n;
3.
For last n rows:
SELECT qty,sales
FROM table
WHERE ROWNUM < (n+1)
ORDER BY sales DESC
For first n rows:
SELECT qty,sales
FROM table
WHERE ROWNUM < (n+1)
ORDER BY sales
4.
For top 10
select * from ( select item,pack_ind from item_master order by item_level)where rownum <= 10
For last 10
select * from store_name where rownum <= (select max(rownum) from store) minus select * from storewhere rownum <= (select max(rownum)-5 from store);
5.
Last ten:
select l, s.* from (select a.*, rownum l from hz_parties a) s where l > (select max(rownum) - 10 from hz_parties);
first ten
select l, s.* from (select a.*, rownum l from hz_parties a) s where l < (select min(rownum) + 10 from hz_parties);
6.
For first n row
select * from emp where rownum <=n
For last n row
select * from emp
minus
select * from emp where rownum <=( select count(*)-n from emp)
7.
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a < 5 --- say n is 5 display first 5 records
For last n rows:
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) xWHERE x.a < 5 FROM )WHERE .a<This query can be used for finding nth row also say n = 5
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a = 5
8.
To select last record from a table
select * from department except
select top ((select count(*) from department)-1) * from department
9.
9.
For Last N Rows:
This can be accomplished in following way:
Example: table - emp
For First n rows:
SELECT * FROM Example: table - emp
For First n rows:
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a < 5 --- say n is 5 display first 5 records
For last n rows:
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) xWHERE x.a < 5 FROM )WHERE .a<This query can be used for finding nth row also say n = 5
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a = 5
2.
T-SQL :
SELECT TOP n FROM Table_Name;
MySQL:
SELECT * FROM Table_Name LIMIT 0, n;
3.
For last n rows:
SELECT qty,sales
FROM table
WHERE ROWNUM < (n+1)
ORDER BY sales DESC
For first n rows:
SELECT qty,sales
FROM table
WHERE ROWNUM < (n+1)
ORDER BY sales
4.
For top 10
select * from ( select item,pack_ind from item_master order by item_level)where rownum <= 10
For last 10
select * from store_name where rownum <= (select max(rownum) from store) minus select * from storewhere rownum <= (select max(rownum)-5 from store);
5.
Last ten:
select l, s.* from (select a.*, rownum l from hz_parties a) s where l > (select max(rownum) - 10 from hz_parties);
first ten
select l, s.* from (select a.*, rownum l from hz_parties a) s where l < (select min(rownum) + 10 from hz_parties);
6.
For first n row
select * from emp where rownum <=n
For last n row
select * from emp
minus
select * from emp where rownum <=( select count(*)-n from emp)
7.
This can be accomplished in following way:
Example: table - emp
For First n rows:
SELECT * FROM Example: table - emp
For First n rows:
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a < 5 --- say n is 5 display first 5 records
For last n rows:
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) xWHERE x.a < 5 FROM )WHERE .a<This query can be used for finding nth row also say n = 5
SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) xWHERE x.a = 5
8.
To select last record from a table
select * from department except
select top ((select count(*) from department)-1) * from department
9.
In Teradata,
TOP 5 rows:
sel top 2 * from emp order by salary asc;
Last 5 rows;
sel * from
(sel salary,row_number() over
(order by salary desc) rnk
from emp) a
where a.rnk<=5;
9.
For First N Rows:
Code
- SELECT top 5 * FROM table_name
Code
- SELECT * FROM
- (SELECT top 5 * FROM
- table_name
- ORDER BY Columnname DESC)
- ORDER BY Columnname ASC