4 ms·
Is doing something like the author suggests SELECT submission_id , MAX(CASE WHEN attribute='name' THEN value END) name , MAX(CASE WHEN attribute='
by gweinberg 9y ago
Is doing something like the author suggests
SELECT submission_id
, MAX(CASE WHEN attribute='name' THEN value END) name
, MAX(CASE WHEN attribute='email' THEN value END) email
, MAX(CASE WHEN attribute='website' THEN value END) website
FROM form_submissions
GROUP BY submission_id
actually faster than doing the equivalent using joins? I would think the opposite, since you are going to have a unique key on entity and name for sure, and you can't really use the name part of the key in the group by syntax. Or can you?
- Twisell 9y agoOf course you can, GROUP BY take as many columns as you want according to standard. And it’s way faster too on my typical use cases.
- gweinberg 9y agoIt's not the group by that needs to take the key into account, it's the case. I would think you get the result from the where/ group by clause first, but then for each row in that result check if it matches the case criteria. I don't think the case check is going to be able to take advantage of te fact that the name is the second column of a composite key.
- Twisell 9y agoI’m not sure to understand (and again stackexchange is a better place to seek advices) but I still think it doable. If I get you right you just have to put the aggregate function inside a CASE instead of the opposite. One limitation of an aggregate function is that you can’t nest aggregate function in the same SELECT clause. But you can perfectly nest it inside a non-aggregate expression.