CODEWITHSUNDEEP
mysqlselectcomparisonrange
Nick
Nick

Reputation: 31

MySQL query SELECT GREATEST int row VALUE beetween limits

I need to get the count of records in table where the value of the fileds: (ps1,ps2,ps3,ps4,ps5,ps6,ps7,ps8,ps9,ps10) between min and max limits is greatest. And to compare this value to an other filed value d_max.

ex:

SELECT count(*) FROM table WHERE d_max < 
{GREATEST one of (ps1,ps2,ps3,ps4,ps5,ps6,ps7,ps8,ps9,ps10) BEETWEEN 10 AND 20}

Please help.

Upvotes: 1

Views: 611

Answers (2)

Johan
Johan

Reputation: 76723

Not pretty, but it should work:

SELECT COUNT(*) FROM (
  SELECT 
    t0.d_max
    , IFNULL(t1.ps1,0) as ps1, IFNULL(t2.ps2,0) as ps2
    , IFNULL(t3.ps3,0) as ps3, IFNULL(t4.ps4,0) as ps4
    , IFNULL(t5.ps5,0) as ps5, IFNULL(t6.ps6,0) as ps6
    , IFNULL(t7.ps7,0) as ps7, IFNULL(t8.ps8,0) as ps8
    , IFNULL(t9.ps9,0) as ps9, IFNULL(t10.ps10,0) as ps10
  FROM `table` t0
  LEFT JOIN `table` t1 ON (t0.id = t1.id AND t1.ps1 BETWEEN 10 AND 20)
  LEFT JOIN `table` t2 ON (t0.id = t2.id AND t2.ps2 BETWEEN 10 AND 20)
  LEFT JOIN `table` t3 ON (t0.id = t3.id AND t3.ps3 BETWEEN 10 AND 20)
  LEFT JOIN `table` t4 ON (t0.id = t4.id AND t4.ps4 BETWEEN 10 AND 20)
  LEFT JOIN `table` t5 ON (t0.id = t5.id AND t5.ps5 BETWEEN 10 AND 20)
  LEFT JOIN `table` t6 ON (t0.id = t6.id AND t6.ps6 BETWEEN 10 AND 20)
  LEFT JOIN `table` t7 ON (t0.id = t7.id AND t7.ps7 BETWEEN 10 AND 20)
  LEFT JOIN `table` t8 ON (t0.id = t8.id AND t8.ps8 BETWEEN 10 AND 20)
  LEFT JOIN `table` t9 ON (t0.id = t9.id AND t9.ps9 BETWEEN 10 AND 20)
  LEFT JOIN `table` t10 ON (t0.id = t10.id AND t10.ps10 BETWEEN 10 AND 20)
) s
WHERE s.d_max < GREATEST(s.ps1,s.ps2,s.ps3,s.ps4,s.ps5,s.ps6,s.ps7,s.ps8,s.ps9,s.ps10)

Note that this does require an explicit primary key to be set of this table, however if you're using InnoDB you should always use an explicit PK, or suffer serious performance issues.

Edit
See if the following construct would be faster....

SELECT COUNT(*) FROM (
  SELECT 
    d_max
    ,CASE WHEN ps1 BETWEEN 10 AND 20 THEN ps1 ELSE 0 END AS ps1
    ,...
    ,CASE WHEN ps10 BETWEEN ..                           AS ps10
) s
WHERE s.d_max < GREATEST(s.ps1,s.ps2,s.ps3,s.ps4,s.ps5,s.ps6,s.ps7,s.ps8,s.ps9,s.ps10)

Upvotes: 1

Code Prank
Code Prank

Reputation: 4250

Try this:

SELECT COUNT(*) FROM table WHERE d_max < 
(SELECT GREATEST(col1, col2,....) FROM table_name WHERE column_name BETWEEN 10 AND 20;)

Upvotes: 0

Related Questions