SQL select row, update and move to next row, update.. and repeat for all rows

[awyeah]

Hello all!

I am trying to use sql to select a row and then find the minimum and maximum values in the row, do some preprocessing, update the recordset using in visual basic 6.0, and then go ahead select the second row, find the minimum and maximum values again.. and go on, until the last row.

The problem is I am having difficulties using sql select for a ROWS.. I am aware how to do this for COLUMNS.. if I want to extract the first row from a recordset, find the min max values, and then, extract the second row and so on.. how will it be possible? (discouraged to use VBA, since i am limited to VB only).. any ideas? sample code will be very helpful.

Here is an example of my recordset:

Col1	Col2	Col3	Col4	Col5
0.4353	0.0563	0.7435	0.9251	0.0325
0.2843	0.0032	0.8243	0.7325	0.9981

This example only lists two rows currently. I want to extract the minimum and maximum values from each row at a time, and then the second row. The reason for extracting minimum and maximum values is because I want to normalize my data in the range from 0 to 1. All help is appreciated.

Logic:
After I find the min and maximum values for each row, I will use the recordset back, read the same row, and update every columns entry with my normalized data value... then proceed to the next row and do the same for every row in my database.

[mdb002]

Assuming Access DB:


SELECT IIf(IIf(IIf(IIf(Col1<Col2,Col2,Col1)<Col3,Col3,IIf(Col1<Col2,Col2,Col1))<Col4,Col4,IIf(IIf (Col1<Col2,Col2,Col1)<Col3,Col3,IIf(Col1<Col2,Col2,Col1)))<Col5,Col5,IIf(IIf(IIf(Col1<Col2 ,Col2,Col1)<Col3,Col3,IIf(Col1<Col2,Col2,Col1))<Col4,Col4,IIf(IIf(Col1<Col2,Col2,Col1)<Col 3,Col3,IIf(Col1<Col2,Col2,Col1)))) AS MaxValue, IIf(IIf(IIf(IIf(Col1>Col2,Col2,Col1)>Col3,Col3,IIf(Col1>Col2,Col2,Col1))>Col4,Col4,IIf(IIf (Col1>Col2,Col2,Col1)>Col3,Col3,IIf(Col1>Col2,Col2,Col1)))>Col5,Col5,IIf(IIf(IIf(Col1>Col2 ,Col2,Col1)>Col3,Col3,IIf(Col1>Col2,Col2,Col1))>Col4,Col4,IIf(IIf(Col1>Col2,Col2,Col1)>Col 3,Col3,IIf(Col1>Col2,Col2,Col1)))) AS MinValue
FROM Table1;

[Slope]

It would probably be easier to use a union query to do something like this. But you need a record id of some sort to group by so that you get the Min and Max values for each row.

SELECT x.RecID, MIN(x.myVal) AS MinVal, MAX(x.myVal) AS MaxVal
FROM
(
SELECT RecID, Col1 AS myVal FROM Table1 UNION
SELECT RecID, Col2 AS myVal FROM Table1 UNION
SELECT RecID, Col3 AS myVal FROM Table1 UNION
SELECT RecID, Col4 AS myVal FROM Table1 UNION
SELECT RecID, Col5 AS myVal FROM Table1
) AS x
GROUP BY x.RecID