web 2.0

Ranking Functions in SQL SERVER 2008


Introduction

Ranking functions are used to provide simple analytics such as statistical ordering or segmentation. In this Article, you learn how to use the four ranking functions that ship with SQL Server 2008.


What Ranking Functions are available in SQL Sever?

T-SQL has four functions that can be used for ranking data:

What is ROW_NUMBER function?

The ROW_NUMBER function assigns a number from 1 to n based on a user-specified sorting order. ROW_NUMBER does not account for ties within the result set, so if you have rows with the same values within the column(s) that you are ordering by, repeated calls to the database for the same result set can produce different row numbering.

T-SQL Syntax for ROW_NUMBER


ROW_NUMBER() OVER (ORDER BY <Column_Name>) 

ROW_NUMBER() OVER (PARTITION BY <Column_Name>)


Examples of ROW_NUMBER

The following examples returns the salesperson along with his or her year-to-date sales and is numbered in descending order according to the year-to-date sales amount:


Using ROW_NUMBER function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using ROW_NUMBER function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


What is RANK function?

If you need to number a result set but also deal with ties, you can use the RANK function. If the result set does not have any ties, RANK produces the same results as ROW_NUMBER.

However, if there are ties, RANK assigns the same value to each row that is tied and then skips to the next value, leaving a gap in the sequence corresponding to the number of rows that were tied.

T-SQL Syntax for RANK


RANK() OVER (ORDER BY <Column_Name>) 

RANK() OVER (PARTITION BY <Column_Name>)


Examples of RANK

The following examples show how RANK is applied to duplicates as well as within each aggregate grouping:


Using RANK function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using RANK function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,RANK() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


What is DENSE_RANK function?

If you do not want any gaps in a sequence, you can use the DENSE_RANK function. DENSE_ RANK assigns the same value to each duplicate but does not produce gaps in the sequence.

T-SQL Syntax for DENSE_RANK


DENSE_RANK() OVER (ORDER BY <Column_Name>) 

DENSE_RANK() OVER (PARTITION BY <Column_Name>)


Examples of DENSE_RANK

The following examples show how DENSE_RANK is applied to duplicates as well as within each aggregate grouping:


Using DENSE_RANK function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DENSE_RANK() OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using DENSE_RANK function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DENSE_RANK() OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


What is NTILE function?

NTILE is used to divide a result set into approximately equal groups. For example, if you wanted to split a result set into six groups with approximately the same number of rows in each group, you could use NTILE(6).

T-SQL Syntax for NTILE


NTILE(integer_expression) OVER (ORDER BY <Column_Name>) 

NTILE(integer_expression) OVER (PARTITION BY <Column_Name>)


Examples of NTILE

The following examples show how NTILE can be used to segment a result set:


Using NTILE function with ORDER BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER (ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


Using NTILE function with PARTITION BY Clause

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER (PARTITION BY a.PostalCode ORDER BY s.SalesYTD) AS 'Rank'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

RESULT:


References

MSDN - SQL Server 2008

Tags: , , , , , , , , ,

SQL Server

Best Catch David Hussey IPL against Kolkata night Raider









Tags: ,

General

How I Got Started in Software Development?



An interesting thread is going around asking how you got started in Software Development. It seems fun and since Mads tagged me, I thought I’d fess up as well.

How old were you when you started programming?

I guess I started programming when I was about 15 or 16 on my Pentium#3. It was nothing too wild. My big project was something I did for the Tech fair at college. At that time I have play with flash action scripting to design slid shows for the tech fair.

How did you get started in programming?

I was always interested in computers growing up and after much begging, my parent’s got me a Pentium#3 in 2000 when i was in 10th. I played tons of games on that thing, but I did begin to get interested in making them do more. When I had the chance to actually learn some programming at College, I knew I’d like it. So the Pentium#3 sparked the interest, and 1st Sem labs got me started doing much more than the very basics.

What was your first Programming Language?

My first programming language was C++.

What was the first real program you wrote?

Real? I guess that tech fair project was real as we have successfully executed the slides at the show.

What languages have you used since?

C, C++, Assembly, FoxPro, Visual Basic, ASP, C#, Vb.NET, ASP.NET, JavaScript, VB Script, XHTML, CSS, PHP, Objective C, Java(BB) and probably a few others I’ve missed.

What was your first professional programming gig?

My first programming job was with a Software development Company. I got a lot of experience there doing everything from requirement analysis, to managing projects, to programming and database management. It was a very interesting job and I stayed there for almost 5 years. I joined them as a trainee and left as Team Lead.

If you knew then what you know now, would you have started programming?

Probably. I still find software development challenge and rewarding. However, I was highly skilled in kicking a soccer ball as a freshman in high school. I was terrible at soccer, but I could shoot hard and place the ball well. If I knew that kicking field goals might make for a nice living, I might have worked in that direction a bit. With a bit of luck (ok, ok, a ton of luck), I’d be about to head off to training camp for my 15th NFL season with the San Diego Chargers. If that had not worked out, I’d probably fall back on Software Development.

What is the one thing you would tell new developers?

Read other peoples code and let them read yours. Reading code is an obvious statement as everyone has searched for a code sample of something or other. However, reading beyond a short snippet to see a larger picture and to learn new ways to do things, not just specific functions. There is just something about a reading through a well thought out project to see how the developers made it work. Also, don’t be afraid to show others your code. Ideally, you can check out ego at the door while you show your code to the other developers. While you might not like the feedback you get all the time, you will often learn new ways of looking at something as well as better ways to do things. Its all about continual improvement!

What’s the most fun you’ve ever had … programming?

Actually programming is my hobby. So every time i do programming i enjoy it. So it is difficult for me to point one. Probably building my first web site. I made a website, sumitgarg.net back in 2007. It may have been the simple website without any extra-ordinary functionality, but I had fun building it and running it. I planed for this website in 2007. At first, I just developed a simple website without any idea about blog. At that time, I thought I’d make a static 4-5 page site, so just to have a online portfolio. It just keep growing and growing and I never really planned it all out. Next, I added a database to allow my friends to log in to website and communicate with me. Shortly afterward, Blog was added. It went on and on. It was fun as I never knew what it was going to need next and often my friends would work with me helping me to see the best way for the site or admin features to work. My favorite feature was setting it up to integrate twitter and facebook widget. Its really nice to see all your friends contacted through one single website. Now i could see all activities at one place.

Tags: , , , ,

General

Welcome to my blog!!



Welcome to the site of an IT professional. I am an ASP.NET, C#, SQL Server Developer with over 5 years experience. I am proficient at all aspects of website development, including: Graphics Design and Page Layout using Master Pages, C# Programming, SQL Server Database Development, and Search Engines Positioning and Optimization.

This site is not a professional project. The purpose behind this site is, to provide my friends, always connected to me. I am, as an IT professional, too busy to even talk to my friends. But i really miss all those good friends. So i have created this site for all those friend who are not in touch with me now days.I want to tell them that i have not forget them and never do that.

This site contains all the stuff about me. The cool pictures reminding me the days with my friends. Every picture itself describes the story of my days with my friends. Reminding me our Advantageous tours. Hey friends, never forget to view all the pics here. You may be in many of these.

Other kool part of this site is my Friends Club. Have you joined it????...What you said???....Nooooooooo. Buddy first do it. Be the member of this site and enjoy more kool features like buddy chat.

Tags: ,

General