How to select columns based on multiple conditions?

Question

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?

Your table mytemp still has two columns, price1 and price2. And you want to produce rows where one of the prices is filled and the other is empty? So if a mytable row's timestamps both match, you want to split this in two rows? (At least this is what your inserts do.) And what does "#repeat for all price-timestamp tuples" mean? It may help a lot, if you show sample data and expected result. — Thorsten Kettner
– Thorsten Kettner, Commented Mar 7, 2019 at 11:42

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-07 11:34:29Z

3

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

answered Mar 7, 2019 at 11:34

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ajan Balakumaran Over a year ago

Correct me If I'm wrong. This query will result in insertion of one row but that is not what OP's current queries are doing right?

Fahmi · Accepted Answer · 2019-03-07 11:33:54Z

1

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

answered Mar 7, 2019 at 11:33

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-03-07 11:55:30Z

0

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.

answered Mar 7, 2019 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

snfrox · Accepted Answer · 2019-03-07 11:55:49Z

0

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

edited Mar 7, 2019 at 11:55

answered Mar 7, 2019 at 11:44

snfrox

1247 bronze badges

2 Comments

membersound Over a year ago

price2 is missing in the results for this.

Thorsten Kettner Over a year ago

Don't use double quotes for string literals. Use single quotes as defined in the SQL standard.

Collectives™ on Stack Overflow

How to select columns based on multiple conditions?

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related