Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

How to calculate Median in SQL Server

Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. – sample table:create table People(Person varchar(1) primary key,City varchar(10),Age int)go– with some sample data: insert into People select 'A','Boston',23 union all – odd #select 'B','Boston',43 union allselect 'C','Boston',29 union allselect 'D','Chicago',15 union all – single #select 'E','NY',12 union all – even #select 'F','NY',55 union allselect 'G','NY',57 union allselect 'H','NY',61go– here's our query, showing median age per city: select city,AVG(age) as MedianAgefrom (select City, Person, Age, ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,COUNT(*) over (partition by City) as CityCountfromPeople) xwherex. Read more →

UNPIVOT: Normalizing data on the fly

Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. Read more →

Top N Percent per Group

Here's a good question in the feedback from my post about using the T-SQL 2005 features to return the Top N per Group of a result set: Sani writes: What about Top n Percent per Group? Read more →

SQL Server 2005: Specifying Partitions for Aggregate Functions

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statement?  For example: select customerID, productID, orderDate, orderAmount, sum(orderAmount) OVER (Partition by CustomerID) as CustTotalfrom OrderscustomerID productID orderDate orderAmount CustTotal———– ———– ———————– ————- ———1 1 2007-01-01 00:00:00. Read more →

SQL Server 2005: Using PARTITION and RANK in your criteria

The RANK and PARTITION features in 2005 are simply amazing. They make so many "classic" SQL problems very easy to solve. For example, consider the following table: create table Batting(Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))insert into Battingselect 'A',2001,'Red Sox',13 union allselect 'A',2002,'Red Sox',23 union allselect 'A',2003,'Red Sox',19 union allselect 'A',2004,'Red Sox',14 union allselect 'A',2005,'Red Sox',11 union allselect 'B',2001,'Yankees',42 union allselect 'B',2002,'Yankees',39 union allselect 'B',2003,'Yankees',42 union allselect 'B',2004,'Yankees',29 union allselect 'C',2002,'Yankees',2 union allselect 'C',2003,'Yankees',3 union allselect 'C',2004,'Red Sox',6 union allselect 'C',2005,'Red Sox',9 Suppose we would like to find out which year each player hit their most home runs, and which team they played for. Read more →

Lots of great SQL Server 2005 Express Downloads

Have you visited the SQL Server 2005 Express Downloads page lately? http://msdn.microsoft.com/vstudio/express/sql/download/ there are so many great tools that you can download, including Reporting Services and a “Business Intelligence Studio” for designing reports, which really surprised me (since you never could use Reporting Services with the previous free versions of SQL). Read more →