CODEWITHSUNDEEP
mysqlsql
membersound
membersound

Reputation: 86925

How to select columns based on multiple conditions?

I'm having a table that has multiple price-timestamp tuples:

#mytable;
id;<somecolumns>;price1;timestamp1;price2;timestamp2;

I want to export each price-timestamp tuple in a temp table, but only the prices whose timestamp is within a specific time interval:

#mytemp
id;price1;price2;

I could achieve this by repeating the sql for each tuple:

INSERT INTO mytemp (price1)
SELECT price1 FROM mytable WHERE timestamp1 > NOW() - INTERVAL 3 HOUR;

INSERT INTO mytemp (price2)
SELECT price2 FROM mytable WHERE timestamp2 > NOW() - INTERVAL 3 HOUR;

#repeat for all price-timestamp tuples

Question: could I optimize this into one single sql query?

Upvotes: 2

Views: 89

Answers (4)

snfrox
snfrox

Reputation: 124

try this

SELECT price1 , '' as price2 from testa WHERE timestamp1 > NOW() - INTERVAL 3 HOUR
UNION
SELECT '' as price1, price2  as price from testa WHERE timestamp2 > NOW() - INTERVAL 3 HOUR

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use cross join to unpivot the data:

select (case when n.n = 1 then price1
             when n.n = 2 then price2
        end),
       (case when n.n = 1 then timestamp1
             when n.n = 2 then timestamp2
        end)       
from t cross join
     (select 1 as n union all select 2) n
having timestamp > now() - interval 3 hour;

However, your method or the equivalent with union all is likely to be the fastest method if you have an index on timestamp.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

you can use case when

  SELECT
   sum(case when timestamp1 > NOW() - INTERVAL 3 HOUR then price1 else 0 end) as price1,
    sum(case when timestamp2 > NOW() - INTERVAL 3 HOUR then price2 else 0 end)
    as price2
     FROM mytable  ;

but if you want insert just select 

INSERT INTO mytemp (price1,price2)
SELECT
   sum(case when timestamp1 > NOW() - INTERVAL 3 HOUR then price1 else 0 end) as price1,
    sum(case when timestamp2 > NOW() - INTERVAL 3 HOUR then price2 else 0 end)
    as price2
     FROM mytable

Upvotes: 3

Fahmi
Fahmi

Reputation: 37493

try this using case when 

INSERT INTO mytemp (price1,price2)
SELECT max(case when timestamp1 > NOW() - INTERVAL 3 HOUR then price1 end),max(case when timestamp2 > NOW() - INTERVAL 3 HOUR then price2 end) 
FROM mytable

Upvotes: 1

Related Questions