Partition by clause needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Partition by clause needed

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-12 : 00:01:15

Partition by and over can be implemented in sql server 2005

Where the partition by clause effectively work?

Difference between GROUP BY and PARTITION BY in Sql Server 2005

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 00:13:49

In t-sql? For table partition?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 00:16:37

PARTITION BY clause is used for partitioning a table into sets based on a particular field/fields so that we may require some operation to be performed on each set.GROUP BY causes the records to be grouped based on field specified and we may use them for operations like aggregation.

A typical example can be given as

Table
ID Category  Amount
123 Cat1      200
127 Cat2      150
123 Cat1      300
136 Cat3      225
132 Cat2      123
123 Cat3      200
136 Cat2      170
123 Cat2      200

we have two requirements

1. Find total amount of sales done by each person(ID)
2.Find the person who did highest sale for each category

Obviuosly, for 1st one we require aggregating sales amt on basis of each ID and return as a single value per ID . Here is where we use group by as:-

SELECT ID, Sum(Amount)
FROM Table
GROUP BY ID
ORDER BY ID

this gives

ID Category  Amount
123 Cat1      900
127 Cat2      150
132 Cat2      123
136 Cat3      395

Now, for second one we need to find the person(ID) who did largest sale for each category. SO here we require first to split table to sets based on category and then arrange records based on decreasing order of sale amount. here is where PARTITION BY...OVER comes to picture.

SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Amount DESC)as 'RowNo',
ID,
Category,
Amount
FROM Table
ORDER BY Category

gives

RowNo ID Category  Amount
1     123 Cat1      300
2     123 Cat1      200
1     123 Cat2      200
2     136 Cat2      170
3     127 Cat2      150
4     132 Cat2      123
1     136 Cat3      225
2     123 Cat3      200

basically what it did is number the records after splitting by category sets and based on decreasing order of Amount.Retrieveing all records with RowNo=1 gives as person who did largest sale based on each category.

Hope this made you clear the differences b/w GROUP BY & PARTITION BY

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-12 : 00:27:30

Thank you visakh, Kindly can you tell about OVER usage in query. I seen some articles, however i can't get clear idea.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-12 : 00:32:37

Have samples in books online.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-12 : 00:34:25

OVER specifies the range of data over which the function defined (this case ROW_NUMBER()) is to be performed. In my example it causes ROW_NUMBER() to applied over the range of data given by PARTITION clause(sets of categories ordered by decreasing amount).

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-12 : 00:59:11

Thank you very much visakh...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources