Blog Archive

Search This Blog

Thursday, April 19, 2012

Print the last n rows or the first n rows of a table

1.
This can be accomplished in following way:

Example: table - emp

For First n rows:
SELECT * FROM
(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
(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
  1.  
  2. SELECT top 5 * FROM table_name
  3.  
For Last N Rows:
Code
  1.  
  2. SELECT * FROM
  3. (SELECT top 5 * FROM
  4. table_name
  5. ORDER BY Columnname DESC)
  6. ORDER BY Columnname ASC
     
 

Thursday, April 12, 2012

Wednesday, April 11, 2012

Teradata Dignostics Commands

This summary is not available. Please click here to view the post.

Relational Database (RDBMS) and its Features

This summary is not available. Please click here to view the post.

Workstation Types and Available Platforms

This summary is not available. Please click here to view the post.

Teradata Database File System


  • Teradata Database file system is a layer of software between Teradata Database layer and the PDE layer.
  • Teradata Database file system service calls allow Teradata Database to store and retrieve data efficiently and with integrity without being concerned about the specific low level operating system interfaces.
  • The data block is a disk-resident structure that contains one or more rows from the same table and is the smallest physical I/O unit for Teradata Database file system.
  • Data blocks are stored in physical disk space units called sectors which are logically grouped together in cylinders.

Parallel Database Extensions(PDEs) and Trusted Parallel Applications (TPA)


Parallel Database Extensions
It is a software interface layer on top of the operating system. The operating system can be MP-RAS, Linux, or Microsoft Windows.

PDE provides Teradata Database with the ability to:
·         Run Teradata Database in a Parallel Environment.
·         Execute Vprocs.
·         Apply a Flexible Priority Scheduler to Teradata Database Sessions.
·         Debug the Operating System Kernel and Teradata Database using Resident Debugging Facilities.

Trusted Parallel Applications (TPA)
       The PDE provides a series of parallel operating system services to a special class of tasks
       On an SMP or MPP system, the TPA is Teradata Database.

TPA services include:
       Facilities to manage parallel execution of the TPA on multiple nodes.
       Dynamic distribution of execution processes.
       Coordination of all execution threads, whether on the same or on different nodes.
       Balancing of the TPA workload within a clique.
       Resident debugging facilities in addition to kernel and application debuggers.

PDE and MPP Systems
 The PDE also enables an MPP system to:
  • Take advantage of hardware features such as the BYNET and shared disk arrays.
  • Process user applications that were written on non-Trusted Parallel Application (non- TPA) nodes and disks.

Disk Arrays

This summary is not available. Please click here to view the post.

Permanent VS Temporary Tables


Permanent and Temporary Tables
  • Permanent storage of tables is necessary when different sessions and users must share table contents.
  • When tables are required for only a single session, we can request the system to create temporary tables.
  • Using this type of table, we can save query results for use in subsequent queries within the same session.
  • We can break down complex queries into smaller queries by storing results in a temporary table for use during the same session. When the session ends, the system automatically drops the temporary table.
 Global Temporary Tables
  • They exist only for the duration of the SQL session in which they are used.
  • The contents of these tables are private to the session, and System Automatically drops the table at the end of that session.
  • System saves the Global Temporary Table Definition Permanently in the Data Dictionary.
  • The Saved Definition may be Shared by Multiple Users and Sessions with Each Session getting its Own Instance of the Table.
 Volatile Tables
  • If you need a temporary table for a single use only, you can define a volatile table.
  • The definition of a volatile table resides in memory (RAM) but does not survive across a system restart.
  • It improves performance even more than using global temporary tables because the system does not store the definitions of volatile tables in the Data Dictionary.
  • Access-rights checking is not necessary because only the creator can access the volatile table.
Derived Tables
  • A special type of temporary table is the derived table. It is specified in SQL SELECT statement.
  • A Derived Table is Obtained from One or More Other Tables as the Result of a Sub-Query.
  • Scope of A Derived Table is only Visible to the Level of the SELECT statement calling the Sub-Query.
  • Using Derived Tables avoids having to use the CREATE and DROP TABLE Statements for Storing Retrieved Information and Assists in Coding More Sophisticated, Complex Queries.