Tuesday, December 18, 2012

Oracle Tip : Using RANK in Oracle


Scenario:-
----------
Suppose we have a sample table for employee salary details

Table :- EmpSalary

UserID EmpName         SalaryDate              Salary
--------  --------------           --------------            ---------
1            XYZ 01/02/2012       1000
2            ABC 01/01/2012       1500
1            XYZ 01/03/2012       3000
2            ABC 01/02/2012       5000
1            XYZ 01/04/2012       4500
2           ABC 01/03/2012       7000

I want to get the initial salary of employees.

Solution:-
----------
You can use Rank analytic function of Oracle. 

SYNTAX:- RANK() OVER (PARTITION BY fieldname1 ORDER BY fieldname2)

PARTITION BY is Similar to Group by . The RANK function will group by fieldname1 and then Order by fieldname2 and Rank the records from 1 , It also resets when userid changes.


select * from (Select
    UserId,
    EmpName,
    SalaryDate,
    Salary,
    Rank() over (PARTITION BY UserId ORDER BY SalaryDate) Ranks
from
    EmpSalary) where Ranks=1

The Result of the above query is as follows


UserID EmpName         SalaryDate              Salary
--------  --------------           --------------            ---------
1            XYZ 01/02/2012       1000
2            ABC 01/01/2012       1500


There are more Analytic Functions which you can be very useful. You can Check Out Other Analytic Functions Here
read more...

Thursday, December 13, 2012

General Tip: Useful tips for your Samsung Galaxy S3


Enjoy some of the tricks in your handset. Below are some of the useful tricks that you may have missed in your Samsung Galaxy S3.
read more...

Tuesday, December 11, 2012

ASP.NET Tip : Capturing Public IP Address of Client

Scenario: - You want to capture the IP address of the person who is logging in to your application and entering data.

read more...

Asp.Net Tip : Keep Login Session Alive

Scenario: - Suppose you want to keep the Login session alive for hours. But Keeping Session Alive in money related applications is high risk one and it is not recommended.(e.g Banking Applications.)

read more...

Monday, December 10, 2012

C# Tip : Reversing the order of words in a sentence

One of the frequent question in Technical Interviews. Its quite simple to Reverse the order of words in a sentence. 

Here is the Bit

read more...