Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Question Unanswered: simulating median on table columns in SQLserver2000


    I have a an sql table having 10,000 Rows.

    The table has 5 columns. They are S1,S2,S3,S4 and M

    I need the best way to calculate the MEDIAN for each row and store it in M. i.e. M=median(S1,S2,S3,S4)

    I've come across articles where they've calculated medians on table rows, not table columns. But my requirement involves table columns. I guess transposing the columns into rows and then calculating median should be possible, but if I do that for 10000 rows using a cursor, then it would take a loooooong time.

    Please suggest the best way to counter this



  2. #2
    Join Date
    Mar 2004
    While calculating the median on a set is not very difficult using TOP, calculating this for each row is quite different.
    First you would need to swap the columns of each row in ascending order and then get the middle value. The later in your case is quite easy as it will be always the third column.

    You should seriously consider doing this with your front-end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts