Thursday, February 19, 2015

Ranking functions in SQL

Ranking functions are one of the most discussed functions in SQL. It returns ranking value for each row in partition. We have four ranking functions in SQL Server.
  • Row_Number
  • Rank
  • Dense_Rank
  • NTILE

Let us create a table and populate it with some dummy data. It will help to us to understand these ranking function better.


--Create table statement
CREATE TABLE [dbo].[Employee](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [EmpCode] [varchar](10) NULL,
 [Address] [varchar](100) NULL,
 [Salary] [numeric](18, 2) NULL,
 [Dob] [datetime] NULL
) 

--Insert some sample data into employee table
INSERT INTO Employee (Name,EmpCode,Address,Salary,Dob) VALUES
('Mr. A','EC001','New Delhi',60000,'01-01-1983'),
('Mr. B','EC002','Banglore',70000,'01-02-1983'),
('Mr. C','EC003','Pune',70000,'01-03-1983'),
('Mr. D','EC004','Norway',8000000,'01-04-1983'),
('Ms. X','EC005','DC',7000000,'01-05-1983'),
('Ms. Y','EC006','Norwood',6500000,'01-06-1983')



Row_Number

Row_Number returns sequence of numbers over the partition and order them with the column specified in order by block. If there is no partition column specified then it will give a sequence of numbers with respect to order by column specified.


select Row_Number() over(order by Salary) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee

Here we don't have any partition logic. So SQL returns a sequence number from 1 to 6 order by salary.



select Row_Number() over(partition by Address order by Salary) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee



Here for the employee from Norway has sequence number 1 and 2(Highlighted in image). Rest of the employee has only sequence number 1 because they are from different different city.

Rank

Rank gives same rank for each group. The rank of a row is one plus the number of ranks that come before the row.


select Rank() over(order by Address) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee



Dense_Rank

Rank gives same rank for each group, without any gaps in the ranking.


select Dense_Rank() over(order by Address) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee



NTILE

Returns rows in an ordered partition into a specified number of groups.


select NTILE(2) over(partition by Address order by Name) as 'Row Number',
Name,EmpCode,Address,Salary,Dob from Employee


This is all about ranking functions in SQL. I hope this article will help you.
Happy Coding .. :)






 

Monday, February 16, 2015

Singleton Pattern

Singleton is one of the simplest patterns which involved one class and it is responsible for instance itself to make sure it creates only one instance. And Singleton provides a global access point to access that instance. Singleton pattern is very useful when we want create only one instance of a class and used same instance of each call. Best example of Singleton is printer driver class.


There are three main things to implement a Singleton pattern.  
  1. A private constructor. Main objective to have a private constructor is to prevent the class initialization from outside.
  2. A static variable which holds the instance of the class.
  3. A static method which create an instance of the class if the static variable is empty. And return the instance of the class.
 
Below example will explain all about.

using System;

namespace TestSoln
{
    //Singleton implementation
    public class Singleton
    {
        //static variable which holds the instance of the class.
        private static Singleton _instance;

        //private constructor which prevent the class initialization from outside.
        private Singleton() { }

        //creates an instance of the class if the static variable is empty. And return the instance of the class.
        public static Singleton GetInstance()
        {
            if (_instance == null)
                _instance = new Singleton();
            return _instance;
        }

        public void DoSomething()
        {
            Console.WriteLine("I am a Singleton class method");
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("--Start--");

            // calling Singleton class
            Singleton.GetInstance().DoSomething();
            Console.ReadLine();
        }
    }
}




Output:
--Start--                               
I am a Singleton class method