Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: SQL query to compare rows of table comparing a column values

    I have imported a large CSV file into a table. A sample of the table looks like this

    name, eye,point,field,date,seconds,sensitivity*10,slope, intercept,T*1000

    NESSA SONIA ,R,6,4,08-04-09,32658845,160,1.062300e+001,62538,1282
    NESSA SONIA ,R,6,5,20-05-09,36288632,20,5.360101e+000,64036,771
    NESSA SONIA ,R,6,6,02-12-09,53223062,60,3.425260e+000,64590,767
    NESSA SONIA ,L,6,4,08-04-09,32658922,230,4.629489e+000,64382,582
    NESSA SONIA ,L,6,5,20-05-09,36288515,170,2.805373e+000,64901,511
    NESSA SONIA ,L,6,6,02-12-09,53223059,220,3.528252e+000,64694,1022



    I have to compare Right eye and left eye "sensitivty*10" values, with matching point and field values and extract the highest(I mean the rows).
    For example I have to compare first row and fourth as the point, field and date are same and extract the fourth as sensitivity is highest. I have a huge file and need to extract all values. Can Anyone help with the sql query, will be highly appreciated.
    Last edited by ramduvana; 05-06-12 at 12:33.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that your sample data were too little and descriptions of requirement were incomplete.

    For example:
    (1) Isn't it neccesary to compare "name"?
    (2) eye = 'L' had higher "sensitivity*10" in all three pairs in your sample data. No sample data which eye = 'R' had higher "sensitivity*10".
    (3) If both of eye = 'L' and 'R' had same "sensitivity*10", of which do you want to take?
    (4) Are all data paired having eye='L' and 'R', same point, same field, and same date?

    By the way,
    why "sensitivity*10" or "T*1000", and not simply "sensitivity" or "T"?

  3. #3
    Join Date
    May 2012
    Posts
    2
    1) need not compare the "name" as it is same
    2) in some of the rows eye "R" has higher sensitiviy values
    3) both are never same
    4) actually the data is from machine, i named the colums poorly as sensitivity*10.
    5) what i want is to compare the rows with same "point" and "field"(i.e, rows "eye" = "L" and rows eye ="L") and obtain highest sensitivity values
    6) here is what i thought till now and it didnt work

    I have divided into two table the left eye and right eye values and

    "
    select if(t1.sensitivity*10 > t2.sensitivity*10,t1.sensitivity*10,t2.sensitivity *10) as col1
    from table t1
    inner join table t2
    on t1.field = t2.field
    and t1.point = t2.point
    and t1.date = t2.date
    where t1.eye ='R' and t2.eye ='L' "

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) need not compare the "name" as it is same
    You wrote that "I have a huge file and need to extract all values. ".
    I couldn't beleive all data had "NESSA SONIA ," as the leading characters.
    Even if so, it would be better to ignore the string in the results.

    2) in some of the rows eye "R" has higher sensitiviy values
    So, please include such data in sample data.

    3) both are never same
    OK.

    4) actually the data is from machine, i named the colums poorly as sensitivity*10.
    Please don't leave such poor names, if you felt "i named the colums poorly".

    5) what i want is to compare the rows with same "point" and "field"(i.e, rows "eye" = "L" and rows eye ="L") and obtain highest sensitivity values
    Do you realy want only highest sensitivity value for each "point" and "field"?
    No need other columns in the results?
    You wrote in your first post
    For example I have to compare first row and fourth as the point, field and date are same and ...
    PLEASE describe your requirements EXACTLY as much as you can.

    Another issue:
    I thought that you didn't answered my fouth question.
    (4) Are all data paired having eye='L' and 'R', same point, same field, and same date?
    Anyhow,

    Example 1:
    Code:
    /*
    IF  the answer for my question (4) was "Yes"
    AND need only highest sensitivity value, "point", "field", (and date?)
         in the result
    THEN
        the following query might be enough.
    */
    SELECT point
         , field
       /*, date */
         , MAX(sensitivity) AS sensitivity
     FROM  your_table
     GROUP BY
           point
         , field
       /*, date */
    /*
    But, this query might be not what you want.
    */
    Example 2:
    Code:
    /*
    IF  the answer for my question (4) was "No"
    AND need only highest sensitivity value, "point", "field", (and date?)
         in the result
    THEN
        the following query might be enough.
    */
    SELECT point
         , field
       /*, date */
         , MAX(sensitivity) AS sensitivity
     FROM  your_table
     GROUP BY
           point
         , field
       /*, date */
     HAVING
           COUNT(*) = 2
       AND MIN(eye) = 'L'
       AND MAX(eye) = 'R'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •