Thursday, October 7, 2010

Use of sql Partition by clause together with case and count

Wherever you are trying to use aggregate functions in sql server, it will give you error like that a group by clause is necessary. Yes, if you really want to group by, then ok. But what about if you want to see the count of a group but you want to every elements of that group. Group by wont allow you to see other columns that what is specified in the group by clause. But you can. Here is the tips and tricks.

select Class, StudentID, StudentName, count(Clas
s) over (partition by Class)
from StudentTable

The bene
fit of partition by is that you can use partition by for generating as many columns as you want and you can specify a different partition criteria for every column.

See below how partition by is use
d together with the case statement. Here one single column is generated but it is using two different partition by clause, one to determine the case condition and the other is generating the row number value.

select Cla
case count(Class) over (partition by Class)
when 1 th
en null
else Row_Number() over (partition 
by Class order by Class)
from Stud

The output 
will appear like this:

You can find some other useful cases where partition by can be used in the following article.

Wednesday, October 6, 2010

create PROCEDURE Insert_Top as
   CREATE TABLE [dbo].[top](
      [SPID] [float] NULL,
      [Status] [nvarchar](255) NULL,
      [Login] [nvarchar](255) NULL,
      [HostName] [nvarchar](255) NULL,
      [BlkBy] [nvarchar](255) NULL,
      [DBName] [nvarchar](255) NULL,
      [Command] [nvarchar](255) NULL,
      [CPUTime] [float] NULL,
      [DiskIO] [float] NULL,
      [LastBatch] [nvarchar](255) NULL,
      [ProgramName] [nvarchar](255) NULL,
      [SPID1] [float] NULL,
      [REQUESTID] [float] NULL,
      [ModifiedDate] [datetime] NULL CONSTRAINT [DF_top_ModifiedDate]  DEFAULT (getdate())
   INSERT INTO [DPart_P008_Dev].[dbo].[top]
    EXEC sp_who2

Now, create the loop procedure:

    create PROCEDURE Loop_Insert_Top as
    WHILE (1=1) BEGIN
        EXEC insert_top
        WAITFOR DELAY '00:01:00';

To view the records:

   select * from [top]