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

    Question Unanswered: simulating median on table columns in SQLserver2000

    Folks,

    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

    Thanx

    Kiran

  2. #2
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    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
  •