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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

  1. #1
    Join Date
    Apr 2008
    Posts
    13

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

    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:
    Code:
    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.

  2. #2
    Join Date
    Jul 2007
    Posts
    161
    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;
    Last edited by mdb002; 05-02-2008 at 12:52 PM.

  3. #3
    Join Date
    Feb 2008
    Posts
    162
    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.

    Code:
    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
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center