TechDays 2008T-SQL Queries: Tips and Techniques Part 2 of 3

More on T-SQL: Tips and Techniques

This time I will talk about Ranking

There are four functions of ranking, but I will speak of only two: rank() and dense_rank()

Let's imagine that in a competition, two first places. There are two ways to classify the competitors. The first one, the first two competitors classify themselves as first and the third classified as third. If there are three first classified, he fourth competitor will sort itself in fourth. This is the rank() function.

The second way, the third competitor ranks up second, after the first position. This is dense_rank ().

The syntax of the ranking functions:

Rank() over ([<partition by>] <order by>)
Dense_Rank() over ([<partition by>] <order by>)

The <order by> is mandatory and means the column, which will classify and in which direction

The <partition by> is optional and is as a group. He left us classify the expression based on partition

Example:

We want to create a list of all customers around the world and rank them by sales:

WITH customerCTE
AS
(
SELECT Sales.Customer.CustomerID, Sum(Sales.SalesOrderHeader.TotalDue) AS TotalDue, Sales.SalesTerritory.Name AS Region
FROM Sales.Customer INNER JOIN
Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID INNER JOIN
Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID AND Sales.SalesOrderHeader.TerritoryID = Sales.SalesTerritory.TerritoryID
GROUP BY Customer.customerid, SalesTerritory.name
)

SELECT *, Rank() OVER (ORDER BY TotalDue desc) AS Rank
FROM customerCTE


We use a CTE for total sales per customer, and then classify them and we get:

CustomerID        TotalDue              Region     Rank
678                 1179857,4657         Canada         1
697                 1179475,8399         Northwest      2
170                 1134747,4413         Southeast      3
328                 1084439,0265         Southwest      4
514                 1074154,3035         Canada         5
155                 1045197,0498         Canada         6

Now, let's say that what I want is the ranking of sales by region. Simple, use partition:


WITH customerCTE
AS
(
SELECT Sales.Customer.CustomerID, Sum(Sales.SalesOrderHeader.TotalDue) AS TotalDue, Sales.SalesTerritory.Name AS Region
FROM Sales.Customer INNER JOIN
Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID INNER JOIN
Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID AND Sales.SalesOrderHeader.TerritoryID = Sales.SalesTerritory.TerritoryID
GROUP BY Customer.customerid, SalesTerritory.name
)

SELECT *, Rank() OVER (PARTITION BY region ORDER BY TotalDue desc) AS Rank
FROM customerCTE

Using the partition by, I have my work done:

CustomerID        TotalDue              Region         Rank
678                 1179857,4657         Canada          1
514                 1074154,3035         Canada           2
155                 1045197,0498         Canada           3
697                 1179475,8399         Northwest        1
170                 1134747,4413         Southeast        1
328                 1084439,0265         Southwest        1


And that's ranking.
Hope you've understand
Cheers

TechDays 2008T-SQL Queries: Tips and Techniques Part 1 of 3

I attended in the conference T-SQL: Tips and Techniques by Stephen Forte

It was a very good session, where I learned some tips that I will share with you here.


Common Table Expression

Common Table Expression or CTE for short is a new feature in SQL 2005 that returns a temporary result set. It's like a temporary table that lives in the scope of the statement. This new expression is very useful for recursive and nested queries.

A simple example:

WITH ExampleCTE(ProductName, SellPrice)
AS
(
SELECT Descricao, PVPsIVA * 1.21
FROM Artigo
)
SELECT * FROM ExampleCTE

This returns:

ProductName SellPrice
---------------- ---------------
Product1 242.0000
Product2 411.4000
product3 411.4000

Notice that the names of the columns are the ones that are defined in the CTE. Another way to declare the name of the columns is this:

WITH ExampleCTE
AS
(
SELECT Descricao AS ProductName, PVPsIVA * 1.21 AS SellPrice
FROM Artigo
)
SELECT * FROM ExampleCTE


So... lets see the good stuff, recursion! Lets say... I have a table with the product's family descriptions. And the table is recursive with it self. And another table with products. Something like this:

CREATE TABLEdbo.Products
(
ProductId int,
Product_Name nvarchar(50),
FamilyId int,
)

CREATE TABLE dbo.Family
(
FamilyId int,
FamilyName nvarchar(50),
ParentId int,
)

INSERT INTO Family VALUES (1, 'Clouth', NULL);
INSERT INTO Family VALUES (2, 'Food', NULL);
INSERT INTO Family VALUES (3, 'Drinks', 2);
INSERT INTO Family VALUES (4, 'Meat', 2);
INSERT INTO Family VALUES (5, 'Steak', 4);

INSERT INTO Products VALUES (1, 'Steak XL', 5);
INSERT INTO Products VALUES (2, 'Steak SuperBig', 5);
INSERT INTO Products VALUES (3, 'Coca-Cola', 3);
INSERT INTO Products VALUES (4, 'Jeans XPTO',1);

What I want to know is every product that exists in the family and in the sub-families. The hardest part is knowing the sub-families. So, we do this with the CTE:


WITH FamilyCTE(FamilyName, FamilyId, ParentId)
AS
(
SELECT FamilyName, FamilyId, ParentId
FROM Family
WHERE FamilyId = 4
UNION ALL
SELECT Fam.FamilyName, Fam.FamilyId, Fam.ParentId
FROM Family AS Fam INNER JOIN FamilyCTE ON
FamilyCTE.FamilyId = Fam.ParentId
)

This gives us the the family and all the sub-families recursively of the family Meat. After that, it's just join with the table Products and we have all the products from the family Meat.

SELECT Product_Name, FamilyName
FROM Products INNER JOIN FamilyCTE ON
Products.FamilyId = FamilyCTE.FamilyId

If I want another family, just change the familyId in the CTE.


Another good example is finding the persons in a table that have the same name:

WITH CTEPessoas AS (
SELECT min(pessoaid) AS pessoaid, Nome
FROM Pessoa
GROUP BY Nome
HAVING count(*) &gt; 1)

SELECT *
FROM pessoa join ctepessoas ON pessoa.nome = ctepessoas.nome

This gives us all the persons in the table that have the same name.

You can learn more at MSDN - WITH common_table_expression (Transact-SQL)

Json support for IE

As posted by Daniel Fisher at http://www.lennybacon.com/JSONSupportForIE.aspx

Pretty helpful while working on WCF services with JSON serialization and AJAX:

REGEDIT4

[HKEY_CURRENT_USER\Software\Classes\Mime\Database\Content Type\application/json]
"CLSID"="{25336920-03F9-11cf-8FD0-00AA00686F13}"
"Extension"=".json"

Find tables in databases

I found this page with a small query that can be useful:

http://codeproject.wordpress.com/2008/02/28/sql-server-find-any-expression-in-the-database/

It allows us to look for tables,'s view, store procedures ... In any database on the server

Life goes around

It has been two years ago that I started the blog and little has written... Let's see if we can change it!

First, from now on I will write in english, so I can share my experiences with more people.

I work in a company, inforTUCANO SI, in the past two years. Our main business it's software for managing hospital's. We have a product to manage the department of cardiology at hospitals. We also have a product to manage clothing stores and we do some programs to fit the needs of individual customers.

In part, is due to the work that never came to write on the blog, but I think it is time to begin to share what I have learned in these past two years and what I will learn.

In brief, I will put my experience here with the Techdays 2008