INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft SQL Server: Programming FAQ

 Forum Search FAQs Links MVPs

## T-SQL Hints and Tips

 Calculating a Median without use of Cursors by hmckillop faq183-4760 Posted: 22 Jan 04 (Edited 5 Feb 04) This is a different version to AngelWPBTampa's article on how to find the median of a column, I have added how to do the samething without a cursor. -- I have now also added a procedure version which accepts two parameters tablename and column name for which you want to find the median off (see bottom)Cursors can prove to be very inefficient if incorrectly used so take some time to analyse Angel's solution and determine for yourself using profiling which solution would best suit your own needs.For this example I use a table named aa_testtable containing two rows, OID (unique identifier) and TestValue both ints. The process is simple, determine the middle row i.e. rowcount/2, If odd number of records then get the value from that middle ordered row, otherwise (if even ) take the average from the two middle rows.--Declare variables to hold the positions and resultsDeclare @v_rowcount bigintDECLARE @v_odd intDECLARE @v_mid intdeclare @v_result1 decimaldeclare @v_result2 decimal--set the rowcount to be the total number of rows in your tableselect @v_rowcount = count(OID) from aa_testtableSET @v_odd = @v_rowcount % 2        --1 if odd number of recs, 0 if evenSET @v_mid = (@v_rowcount / 2)+1        --Number of the "middle" recordselect @v_mid as MiddleRowset rowcount  @v_mid select @v_result1 = TestValue from aa_testtable order by testvalueif @v_odd = 0begin    set @v_mid = @v_mid -1 --get the previous row    set rowcount  @v_mid     select @v_result2 = TestValue from aa_testtable order by testvalue    set @v_result1 = (@v_result1 + @v_result2)/2end--the current value of @v_result1 is the median of column testvalueselect @v_result1 this is a proc version to find the median and allows two parameters of the tablename and fieldname and will return the median of this field in the table. Note no checks take place to ensure the correct data type etc.CREATE PROC usp_CalcMedian(    @pi_TableName VARCHAR(255),    @pi_MedianRow VARCHAR(255))ASBEGIN    Declare @v_rowcount bigint    DECLARE @v_odd int    DECLARE @v_mid int    declare @v_result1 decimal    declare @v_result2 decimal    DECLARE @v_SQL nvarchar(500)    DECLARE @ParmDefinition NVARCHAR(500)        --set the rowcount to be the total number of rows in your table    SET @v_SQL = 'select @v_rowcountOUT = count(*) from ' + @pi_TableName    SET @ParmDefinition = N'@v_rowcountOUT int OUTPUT'    exec sp_executesql @v_SQL, @ParmDefinition, @v_rowcountOUT= @v_rowcount OUTPUT    SET @v_odd = @v_rowcount % 2        --1 if odd number of recs, 0 if even    SET @v_mid = (@v_rowcount / 2)+1        --Number of the "middle" record        select @v_mid as MiddleRow        set rowcount  @v_mid         SET @v_SQL = 'select @v_result1OUT = ' + @pi_MedianRow + ' from ' + @pi_TableName + ' order by ' + @pi_MedianRow    SET @ParmDefinition = N'@v_result1OUT int OUTPUT'    exec sp_executesql @v_SQL, @ParmDefinition, @v_result1OUT= @v_result1 OUTPUT        if @v_odd = 0    begin        set @v_mid = @v_mid -1 --get the previous row        set rowcount  @v_mid         SET @v_SQL = 'select @v_result2OUT = ' + @pi_MedianRow + ' from ' + @pi_TableName + ' order by ' + @pi_MedianRow        SET @ParmDefinition = N'@v_result2OUT int OUTPUT'        exec sp_executesql @v_SQL, @ParmDefinition, @v_result2OUT= @v_result2 OUTPUT        set @v_result1 = (@v_result1 + @v_result2)/2    end        --the current value of @v_result1 is the median of column testvalue    select @v_result1end Back to Microsoft SQL Server: Programming FAQ Index Back to Microsoft SQL Server: Programming Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!