Feeds:
Posts
Comments

Posts Tagged ‘ROW_NUMBER’

SQL SERVER 2012 – Generating serial numbers for a result set by using sequence object

Posted in Solutions, SQL SERVER, tagged , , , , , on December 29, 2013| 3 Comments »

Generating Serial number is something that is very usual for a SQL Server developer, either it is for report or any result set. I would not be wrong if I said that it is something that we need to use every now and then. I usually use ROW_NUMBER to generate the serial number in most of the places unless otherwise any compatibility issues. However, I wanted to generate it in new ways and recently, I found a new method to generate serial number while playing with Sequence Object.
Given below are scripts to generate a serial number using ROW_NUMBER & SEQUENCE Object.

  • Using ROW_NUMBER
--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
ROW_NUMBER() OVER(ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--OUTPUT

serial number using sequence.1.1

  • Using SEQUENCE Object 
--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
--Create Sequence Object
CREATE SEQUENCE dbo.Seq_Sample
AS int     START WITH 1
INCREMENT BY 1 ;
GO
--Generate serial number using Sequence object
SELECT
NEXT VALUE FOR dbo.Seq_Sample OVER (ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--Reset Sequence object to 1, So it will start from 1
--in the next result set.
ALTER SEQUENCE [dbo].Seq_Sample RESTART WITH 1
GO
--OUTPUT

serial number using sequence.1.1

Read Full Post »

SQL SERVER – How to select minimum value from group in a table

Posted in Solutions, SQL SERVER, tagged , , , , , on December 10, 2013| 1 Comment »

Sometimes, you need to define window frame within the table on the basis of certain criteria, to pick up some specific data. Today, I was developing one report for my customer and the requirement was very simple, to find the people with minimum wage in each department. We do have the solution using RANK(ROW_NUMBER) function. However, we can achieve it using FIRST_VALUE function (Shipped with SQL Server 2012) as well.

Let me create a sample to demonstrate the script.

--This script is compatible with SQL Server 2005 and above.
--Create table
Create Table [Employee]
(
[Employee ID] int,
[Employee Name] varchar(50),
[Department] varchar(50),
[Salary] int
)

GO
--Insert records into table
Insert Into [Employee] Values (1,'Ali','IT',10000)
Insert Into [Employee] Values (2,'Derek','IT',6000)
Insert Into [Employee] Values (3,'Jack','IT',9000)
Insert Into [Employee] Values (4,'Simon','Marketing',5000)
Insert Into [Employee] Values (5,'Monica','Marketing',7500)
Insert Into [Employee] Values (6,'Sandra','Marketing',8000)
GO

Given below are the solutions :

SOLUTION 1 :
This is an old method and we have been using it since SQL Server 2005. We use a Ranking Function namely ROW_NUMBER in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
;With CTE As
(
Select
Row_Number() Over (Partition By Department Order By Salary) As [S.No]
,[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
From dbo.[Employee]
)
Select
[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
from CTE
Where [S.No]=1
--OUTPUT

minimum value in the group.1.1

SOLUTION 2 :
We will use a new analytic function namely FIRST_VALUE in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
;WITH CTE As
(
Select
[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
,FIRST_Value([Salary]) Over
(Partition By [Department] Order By [Salary]) As [minimum Value]
From dbo.[Employee]
)
Select
[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
From CTE
Where [Salary]=[minimum Value]
GO
--OUTPUT

minimum value in the group.1.1

Read Full Post »