Pages

Tuesday, March 11, 2014

SQL: how to display nth record from table



To display nth row from a table say Emp: 

select * from Emp where rownum< (n+1) MINUS  select * from Emp where rownum < n


First select query will return first N rows and second select query will return first N-1 rows and then subtract these two results which gives only nth row in the table.
It is similar to Minus operation over sets

For example if you want to display 5th row from emp table then:
select * from emp where rownum< 6 MINUS  select * from emp where rownum < 5

SQL: Display 5th row from a table
SQL Minus Operation

 
 
 

Friday, March 7, 2014

how to select duplicate records in sql

First we do grouping of all the records in the table then display only those records whose count is greater than 1.
For more more details see the Emp table and Output result

Query: Select sal from emp group by sal having count(sal) > 1

Displays all the salaries which are duplicate in Emp table.

Image will not be available
SQL 

Thursday, March 6, 2014

How to multiply columns in sql



Temp is a table containing number as column name and if you want to multiply all the values in number column..then simply run the following command.

Select exp(sum(ln(number))) from Temp;


Example:
Number
1
2
3


exp(sum(ln(Number))) = exp(ln1+ln2+ln3) = exp(ln(1*2*3)) = 1*2*3 = 6

Thursday, February 27, 2014

How to delete duplicate records in sql

Delete from Student a1 where a1.rowid < (select max(rowid) from Student a2 where a2.name=a1.name);


Explaination:

For each row in Outer query, inner query runs for complete table checking the equal condition and returns maximum row id and if this maximum row id is greater then the row-id of outer query then delete the row. and this is done for each row of outer query.