Pages

Wednesday, 26 December 2012

FOR XML Clause


FOR XML CLAUSE:  Sometimes we need to data in form of XML from Database.SQL Server provide FOR XML Clause. For XML clause returns result of query as XML.There are some options with FOR XML clause
  • Raw
  • Path
·         Auto
  • Elements(can be used with raw,auto,path)
  • Explicit

You will find all differences from below query output

CREATE TABLE [dbo].[emp](
      [emp_id] [nchar](10) NULL,
      [emp_name] [nchar](10) NULL
) ON [PRIMARY]

insert into emp (emp_id,emp_name) values
('1','ABC'),
('2','XYZ'),
('3','PQR')


  1. select *from dbo.emp for xml raw

<row emp_id="1         " emp_name="ABC       " />
<row emp_id="2         " emp_name="XYZ       " />
<row emp_id="3         " emp_name="PQR       " />


  1. select *from dbo.emp for xml raw,elements

<row>
  <emp_id>1         </emp_id>
  <emp_name>ABC       </emp_name>
</row>
<row>
  <emp_id>2         </emp_id>
  <emp_name>XYZ       </emp_name>
</row>
<row>
  <emp_id>3         </emp_id>
  <emp_name>PQR       </emp_name>
</row>


  1. select *from dbo.emp for xml auto

<emp emp_id="1         " emp_name="ABC       " />
<emp emp_id="2         " emp_name="XYZ       " />
<emp emp_id="3         " emp_name="PQR       " />


  1. select *from dbo.emp for xml auto,elements


<emp>
  <emp_id>1         </emp_id>
  <emp_name>ABC       </emp_name>
</emp>
<emp>
  <emp_id>2         </emp_id>
  <emp_name>XYZ       </emp_name>
</emp>
<emp>
  <emp_id>3         </emp_id>
  <emp_name>PQR       </emp_name>
</emp>


  1. select *from emp for xml auto

<emp emp_id="1         " emp_name="ABC       " />
<emp emp_id="2         " emp_name="XYZ       " />
<emp emp_id="3         " emp_name="PQR       " />


  1. select *from emp for xml path('uma')

<uma>
  <emp_id>1         </emp_id>
  <emp_name>ABC       </emp_name>
</uma>
<uma>
  <emp_id>2         </emp_id>
  <emp_name>XYZ       </emp_name>
</uma>
<uma>
  <emp_id>3         </emp_id>
  <emp_name>PQR       </emp_name>
</uma>




Prev--->Basics of Index in SQL Server                                                 Next--->Handling Null values in SQL Server

Wednesday, 19 December 2012

Find Nth highest salary using RANKING functions


Suppose you want to find nth highest salary from a table,Then you can use DENSE_RANK() function.Here I am showing some example for achieving the same.           

DENSE_RANK(),RANK(),ROW_NUMBER(),NTILE() in SQL Server:


CREATE TABLE [dbo].[Person1](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [name] [varchar](20) NULL,
     [sal] [int] NULL
) ON [PRIMARY]

Insert Data into table
insert  into Person1  (name,sal)values('Anil','100000')
insert  into Person1  (name,sal)values('Patel','100000')
insert  into Person1  (name,sal)values('Chikoti','300000')
insert  into Person1  (name,sal)values('Nikol','100000')
insert  into Person1  (name,sal)values('Rajeev','100000')
insert  into Person1  (name,sal)values('Binod','300000')
insert  into Person1  (name,sal)values('Pradeep','500000')
insert  into Person1  (name,sal)values('Dinel','300000')
insert  into Person1  (name,sal)values('Gemil','400000')

Brief about These: check MSDN
·         ROW_NUMBER():Returns rownumber of each row of result set.
Syntax:
ROW_NUMBER ( )
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
·        DENSE_RANK(): Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Syntax:
DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )


·         RANK(): Returns the rank of each row within the partition of a result set.
Syntax:
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Now see result
 select
id,
name,
sal,
DENSE_RANK() over (order by sal) as [DENSE_RANK],
RANK() over (order by sal) as [RANK()],
ROW_NUMBER() over (order by sal) as [ROW_NUMBER()]
  from Person1
      OUTPUT:

Now if we want to find name that gets 2nd highest salary then execute this query
DENSE_RANK() can be used for this purpose because it provide a number for same values.
SELECT
a.Name,a.sal
FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM Person1) as a where rk=2

Note: you can use over(ORDER BY sal desc) or over(ORDER BY sal ascfor highest and lowest salary.












If you want to 3rd highest then put rk=3 and as you wish.
SELECT
a.Name,a.sal
FROM(SELECT Name,sal,DENSE_RANK() over(ORDER BY sal) AS rk FROM Person1) as a where rk=3