Archive for March 5th, 2010

CASE Expression in SQL Server 2005

SQL CASE Expression can be used effectively and efficiently with SELECT, UPDATE, DELETE Command or with WHERE, HAVING Clause. Here, I am going to show the use of CASE with SELECT Command.

CREATE TABLE t_Education
(
EducationID int IDENTITY(1,1) NOT NULL,
ApplicantID int NOT NULL,
ExamName nvarchar(20) NULL,
MajorName nvarchar(20) NULL,
ResultCode smallint NULL,
CGPA float NULL,
OutOfCGPA float NULL
)
Continue reading

Advertisements

Row Number in SQL Server 2005

In SQL Server 2005, you can use built in ROW_NUMBER() or RANK() function to generate row number for selected record set.

Syntax:

ROW_NUMBER () OVER (ORDER BY column name)

RANK() OVER (ORDER BY column name)

Example:

ROW_NUMBER ():

SELECT ROW_NUMBER()
OVER (ORDER BY applicantInformation_Name DESC) AS SerialNo,
applicantInformation_Name,
applicantInformation_Email,
applicantInformation_Phone_No
FROM t_ApplicantInformation

RANK():

SELECT RANK()
OVER (ORDER BY applicantInformation_Name DESC) AS SerialNo,
applicantInformation_Name,
applicantInformation_Email,
applicantInformation_Phone_No
FROM t_ApplicantInformation

Output:

Those two quires produce the same result.

Thanks
A Rahim Khan