presto multiple columns in subquery not supported

Question

Just getting my head around presto (and strange table structures) but assuming I have a table that stores data like this:

I'm looking to write a query to get the following as output:

I tried to get through this using below query:

SELECT DISTINCT

ts.id,

(SELECT
ts.trial_type,
ts.trial_score

FROM trial_scores ts

WHERE ts. = 12345678
AND ts.date = date_format(date_add('day', -3, CURRENT_DATE), '%Y-%m-%d')) as previous_score,

(SELECT
ts.trial_type,
ts.trial_score

FROM trial_scores ts

WHERE ts. = 12345678
AND ts.date = date_format(date_add('day', -2, CURRENT_DATE), '%Y-%m-%d')) as current_score

FROM trial_scores ts

However, I keep getting an Error from Query Engine "Multiple columns returned by subquery are not yet supported. Found 2"

Are there any other ways to re-write this query to get the desired output above?

Can confirm both my inner subqueries run on their own so I guess it's a case of mushing them together with everything else that I can't get my head around.

I have news for you. It is not just PrestoDB where this fails. It will fail in most databases (although there is an exception or two). Your syntax is not correct either. What is `ts.`? — Gordon Linoff, Apr 24 '20 at 13:59
@GordonLinoff that's just the alias i gave the trial_scores table — InlLad, Apr 24 '20 at 14:05
One (database agnostic) way to subquery two columns is https://stackoverflow.com/questions/5686271/selecting-multiple-columns-fields-in-mysql-subquery — darw, Jun 10 '22 at 10:17

score 1 · Accepted Answer · answered Apr 24 '20 at 14:03

I suspect you just want conditional aggregation.

SELECT ts.id,
       MAX(CASE WHEN ts.date = date_format(date_add('day', -3, CURRENT_DATE), '%Y-%m-%d') THEN ts.trial_type END) as prev_trial_type,
       MAX(CASE WHEN ts.date = date_format(date_add('day', -3, CURRENT_DATE), '%Y-%m-%d') THEN ts.trial_scoree END) as prev_trial_score,
       MAX(CASE WHEN ts.date = date_format(date_add('day', -2, CURRENT_DATE), '%Y-%m-%d') THEN ts.trial_type END) as curr_trial_type,
       MAX(CASE WHEN ts.date = date_format(date_add('day', -2, CURRENT_DATE), '%Y-%m-%d') THEN ts.trial_scoree END) as curr_trial_score
FROM trial_scores ts
GROUP BY ts.id;

super thanks! this did the trick. (funny enough I tried this iteration earlier but kept getting null values for the previous scores as my case statement was against both the score type and date) — InlLad, Apr 24 '20 at 14:13

presto multiple columns in subquery not supported

1 Answers1