Tuesday, December 31, 2013

Happy New Year 2014



                              HAPPY NEW YEAR 2014



Wishing all of the readers of this blog a very happy New Year. Thanks for supporting me and my blog last year 2013. I hope this support will continue this year too. May God bless all of you with lots of lots of success. 

Sunday, December 29, 2013

Deleting Duplicate rows using CTE

Many times we caught in a situation where we have a table where there is no primary or unique column defined in it and rows with duplicate data inserted in the table. In this example I will tries to show how to delete the duplicate rows using CTE.

Suppose we have a table name "tbl_dublicatedata" whose table creation script is given below:-

Create table tbl_dublicatedata (name nvarchar(100), age int, salary int)

Now we insert duplicate data into this table. Insert script for these rows are given below:-

Insert  into tbl_dublicatedata
select 'Vivek', 28, 800000
union all
select 'Avinash', 29, 600000
union all
select 'Vivek', 28, 800000

If we want to see the data into the table , we can run the following query

select * from  tbl_dublicatedata 

This query will return the following result




With the help of the CTE we can remove these duplicate records. 

With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1  

If we again see the data in the table tbl_dublicatedata using the same query which we used earlier we will get the following result:-

select * from  tbl_dublicatedata 





From the result we can see that the duplicate rows are deleted and there is no duplicate data in the table.

DMCA.com

Saturday, December 28, 2013

Answer -Question of the Week - 25/12/2013- Question 1


Question:- Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid  Studentname
1               Vivek Johari
2               Chandra Singh
4               Avinash Dubey
6               Akhil
7               Sunny Rana
10             Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table


Answer:-


 Thanks all for answering that question. I really appreciate the effort of all the people who tried and gives the response.We get multiple answers for this question in which correct answer is given below:-

Correct answer in case the first coulmn is an identity column

Answer given by Arjan  Dingemans


WITH Numbers AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < IDENT_CURRENT('tbl_students')
)
SELECT Number
FROM Numbers N
LEFT JOIN tbl_students S ON N.Number = S.StudentId
WHERE S.StudentId IS NULL


Answer given By Chandra Prakash Singh

select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number <= IDENT_CURRENT('tbl_students')
and tbl_students.studentid is null and number > 0


Correct answer in case the first coulmn is not an identity column 

Answer given By Avinash Dubey

WITH DeletedRows (missnum, maxid)
AS

SELECT 1 AS missnum, (select max(Studentid) from tbl_Students) UNION ALL SELECT missnum + 1, maxid FROM DeletedRows 
WHERE missnum < maxid
)
SELECT missnum FROM DeletedRows
LEFT OUTER JOIN tbl_Students e on e.Studentid = DeletedRows.missnum
WHERE e.Studentid is NULL OPTION (MAXRECURSION 0)


Answer given by Aviral Lamba

select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number < (select MAX(studentid) from tbl_students)
and tbl_students.studentid is null and number > 0 


Also I appreciate the effort done by chetan Deshpande in attempting the question

DMCA.com

Question of the Week - 28/12/2013 - Question 2

Question:- Suppose we have a table named tbl_charactername have only one column say "charactername".
It contains 10 rows. First 5 rows contains "Vivek Johari" and the next 5 rows contains "Abhinav Golwalkar"

tbl_charactername
---------------------
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar

Now we need a SQL Query which can return the result in the form

Desired Result:-

Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar

Query should work even if the table has 30 rows with first 15 contains "vivek johari" and next 15 contains "Abhinav Golwalkar" or 40 rows with first 20 contains "vivek johari" and next 20 contains "Abhinav Golwalkar" etc. But output should contains "Vivek Johari" and "Abhinav Golwalkar" in alternate rows.

Keep Answering.......:-)

Wednesday, December 25, 2013

Question of the Week -25/12/2013- Question1

Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid   Studentname 
1               Vivek Johari
2              Chandra Singh
4              Avinash Dubey
6              Akhil 
7              Sunny Rana
10            Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table.
DMCA.com

Ranking Functions in SQL Server

Ranking Function:- Sometimes we need to provide a Row number to the rows in a table or within a partition. For example, suppose we want to give rank to sales man according to their sales amount in a particular month/year. For this purpose, SQL server provides us Ranking functions. Ranking functions are used to provide a Rank to a row in a given partition.

There are 4 Ranking functions provided by SQL server

1) ROW_NUMBER()

2) RANK ()

3) DENSE_RANK()

4) NTILE()

In this article, I am going to explain these functions with the help of the table "tbl_companysales". Queries for creation and insertion of data into this table is given below:-

Create Table tbl_companysales(id int identity(1,1), employeeid int,  employeename nvarchar(150), month_name nvarchar(100), year int,salesamount int)

Insert Into tbl_companysales (employeeid,employeename,month_name,year,salesamount)
Select 1, 'Vivek' ,'April', 2012, 200000
union all
Select 2, 'Ravi', 'April', 2012, 150000
union all
Select 3, 'Uma', 'April', 2012, 150000
union all
Select 4, 'Raman','April', 2012, 140000
union all
Select 1, 'Vivek', 'October', 2012, 300000
union all
Select 2, 'Ravi', 'October', 2012, 200000
union all
Select 3, 'Uma', 'October', 2012, 400000
union all
Select 4, 'Raman','October', 2012,300000
union all
Select 5, 'Monika', 'October', 2012, 300000
union all
Select  1, 'Vivek' , 'November', 2012, 300000
union all
Select 2, 'Ravi', 'November', 2012,200000
union all
Select 3, 'Uma', 'November', 2012, 200000
union all
Select 4, 'Raman', 'November', 2012, 200000
union all
Select  5, 'Monilka', 'November', 2012, 400000

We can use the following query to know the rows contains by this table

select  *   from tbl_companysales


Sample data for Ranking function Article


ROW_NUMBER():- This function gives the sequential number of a row within a partition of a resultset, starting from 1.

Syntax :-

   ROW_NUMBER ( )     OVER ( [ ] )

Example:-  If we want to gives rank to employee within a month, we can use the following query

Query:-

SELECT employeename,month_name ,ROW_NUMBER () OVER (partition by month_name ORDER BY month_name) AS "Row Number"  ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-


If we see the result, we found that the function ROW_NUMBER() gives every row a number within a partition which is in case is month.
RANK():- This function return the rank of a row within a partition of a result set

Syntax:-

RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )

Example:-Suppose if we want to get the rank of employee according to their sales amount within a month, we can use the following query

Query:-

SELECT employeename,month_name ,RANK() OVER (partition by month_name ORDER BY salesamount) AS Rank ,salesamount FROM tbl_companysales

Result:-
This query will gives the following result:-



 If we see that result we found that RANK() function gives each rows a rank based on the sales amount value within a partition (in this month). Also we see that if more than one rows have the same sales amount then it gives same rank to all the rows containing the same sales amount value within a partition and for the next row it gives the next rank with a gap. For example in the above result, Vivek get the rank 4th in month April since it comes on 4th number instead of 3 and two rows in April with employees Ravi and Uma get the same rank.


DENSE_RANK():- This function returns the rank of a row within a partition of a result set similarly to Rank function but it return the rank of a row without any gap.

Syntax:-

DENSE_RANK ( )    OVER ( [ ] < order_by_clause > )

Example:-Suppose if we want to get the dense rank of employee according to their sales amount within a month, we can use the following query

Query:-

SELECT employeename,month_name ,DENSE_RANK() OVER (partition by month_name ORDER BY salesamount) AS "Dense Rank" ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-



If we see the result , we found that Dense_Rank() function gives Rank without any gap as it gives 3rd rank to employee Vivek in month April.


NTILE():- This function distributes the rows in an ordered partition into a specified number of groups. For each row, NTILE() will returns the number of the group to which the row belongs.

Syntax:-

NTILE (integer_expression)    OVER ( [ ] < order_by_clause > )

Example:- 
  In the following example, we divides the rows into 4 groups. since the number of rows are not fully divisible by 4 so it creates first two groups of 4 rows and next 2 groups of 3 rows each 

Query:-

SELECT employeename,month_name ,NTILE(4) OVER (ORDER BY month_name) AS Quartile ,salesamount FROM tbl_companysales

Result:-

This query will gives the following result:-




If we see the result, we found that the Ntile() function divides the rows into 4 groups. Employees in the first group are given the rank 1 since they belong to first group. similarly are employees are given the rank according to the number of the group to which they belong. 

Summary :- In this article , I tried to explain the concepts of Ranking functions in SQL Server with the help of examples. We mostly used the functions Row_number() and Rank(). This function is also used in deleting the duplicate records in a table. I am waiting for your valuable comments (feedback) on this article. If you have any query regarding this, you can send me a mail at askvivekjohari@gmail.com. 

Keep reading and keep visiting my blog :-)



DMCA.com