How to find the nth lowest values in a row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find the nth lowest values in a row

Author

Topic

willcas
Starting Member

7 Posts

Posted - 2007-06-05 : 04:39:57

Morning All,

I have a need to find the 5th and 6th lowest values in a row of numerical data so that I can perform a calculation on them.

Currently I transpose the data ie turn the row into a column and then using rowcount and ordering I can get the values I need.

This works well , but if I could get rid of the transpose step it would obviously be quicker.

Does anybody have any ideas or pointers of how to go about it ? if someone had a prewritten function/SP that would be sensational.

Thanks

Will

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 04:48:22

select <col-list> from (
select <col-list>, row_number() over (partition by <col-list> order by <col-list>) AS recid
) as d
where recid between 5 and 6

Peter Larsson
Helsingborg, Sweden

willcas
Starting Member

7 Posts

Posted - 2007-06-05 : 04:56:22

Thanks Peter I'll give it a go.

W

willcas
Starting Member

7 Posts

Posted - 2007-06-05 : 06:04:56

Hey Peter,

I havent made any progress can you give me a little more insight into how it works if you have the time

Just to clarify I have perhaps 3 rows of data not in any order
1,9,2,3,4,5,6,8,7 from this row I want to return 5 and 6
6,7,8,12,9,10,5,11,13 from this row I want to return 9 and 10
35,36,37,38,39,40,41,42,43 from this row I want to return 39 and 40
etc

Thanks again
Will

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 06:42:56

Use the function found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Peter Larsson
Helsingborg, Sweden

willcas
Starting Member

7 Posts

Posted - 2007-06-05 : 07:40:33

Peter,

I was using the commas to denote a new field in a row. Its not a single field of comma separated values.

W

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 08:59:13

SELECT q.RowID, q.Value
FROM (
SELECT d.RowID, d.Value, ROW_NUMBER() OVER (PARTITION BY d.RowID ORDER BY d.Value) AS RecID
FROM (
SELECT RowID, Col1 AS Value FROM Table1 UNION ALL
SELECT RowID, Col2 FROM Table1 UNION ALL
SELECT RowID, Col3 FROM Table1 UNION ALL
SELECT RowID, Col4 FROM Table1 UNION ALL
SELECT RowID, Col5 FROM Table1 UNION ALL
SELECT RowID, Col6 FROM Table1 UNION ALL
SELECT RowID, Col7 FROM Table1 UNION ALL
SELECT RowID, Col8 FROM Table1 UNION ALL
SELECT RowID, Col9 FROM Table1 UNION ALL
SELECT RowID, Col10 FROM Table1
) AS d
) AS q
WHERE q.RecID BETWEEN 5 AND 6

Peter Larsson
Helsingborg, Sweden

willcas
Starting Member

7 Posts

Posted - 2007-06-05 : 09:34:17

Peter,

I tried this before and because there are over 500 columns in the table it fails, you get a message , Too many table names in the query. The max is 256.

Perhaps I'll stick with my current solution as it works and concentrate on the indexing to speed it up.

If you have any other suggestions though I'd be glad to hear them.

W

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-05 : 09:36:52

500 columns?
I think you better redesign your table/database/application...

Peter Larsson
Helsingborg, Sweden

willcas
Starting Member

7 Posts

Posted - 2007-06-05 : 11:10:29

Peter,

I would agree with you normally but not in this case, the data is a vector of financial risk data per position that needs to be scanned in its entirety for correct results. Its not relational data.

Lets just say its been an interesting challenge working with this data. You could argue that sql isnt the place to handle or store this data but that conversation would take at least to 4 beers to talk through.

Thanks for the input.
W

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-08 : 06:10:03

Looking forward to the

!!!

Peter Larsson
Helsingborg, Sweden

willcas
Starting Member

7 Posts

Posted - 2007-06-11 : 05:10:25

Next time you're in London Peter.

W

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources