How to find nth lowest value in a row

Morning all,
I have a table with 500+ fields of numeric data (a time series of values) I
import from an external system. I need to find the 5th and 6th lowest values
in a given row to perform some calculations.
I currently do this by transposing the data row ie turning the row into a
column sorting it and then using row_num to get the 5th and 6th lowest
values. This transpose is a real bottleneck on my process.
Does anyone have any alternatives to my method ? All suggestions gratefully
received.
Thanks
WOn Thu, 16 Aug 2007 17:56:35 -0700, willcas wrote:
>Morning all,
>I have a table with 500+ fields of numeric data (a time series of values) I
>import from an external system. I need to find the 5th and 6th lowest values
>in a given row to perform some calculations.
>I currently do this by transposing the data row ie turning the row into a
>column sorting it and then using row_num to get the 5th and 6th lowest
>values. This transpose is a real bottleneck on my process.
>Does anyone have any alternatives to my method ? All suggestions gratefully
>received.
Hi W,
Perhaps you could transpose the data during the import and store it in a
more relational way?
The only other alternative would be a really very nasty (and long!) CASE
epxression that I won't even begin to think about <shudder>.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W
I would treat this as a staging table and transform the data to a properly
normalized model. Then write your query against that new model.
Perhaps better still, do the transformation before the load (using
Integration Services for example).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can't you use unpivot for the transposition?
"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W