This might be more of a SQL query but how would you go about Apache Pinot #troubleshooting

This might be more of a SQL query, but how would y...

Mark Needham

01/25/2022, 2:46 PM

This might be more of a SQL query, but how would you go about filtering based on an aggregated value? e.g. I have this query:

Copy code

select competitorId, max(distance) AS distanceCovered
from parkrun 
group by competitorId
order by distanceCovered DESC

And I want to only return records where

distanceCovered

is greater than say 1,000. But it doesn't like that. What's the proper way to solve this type of problem?

Anish Nair

01/25/2022, 4:01 PM

Hey @Mark Needham , for this you should be using HAVING Clause.

Mark Needham

01/25/2022, 4:02 PM

ahhhh

Mark Needham

01/25/2022, 4:02 PM

instead of where?

Anish Nair

01/25/2022, 4:03 PM

Yeah. For filtering on aggregated values , having can be used

Mark Needham

01/25/2022, 4:04 PM

sweet! Thanks 🙂

Mark Needham

01/25/2022, 4:08 PM

while we're here, I may as well ask another question. So I have this query:

Copy code

select competitorId, max(rawTime) AS rawTime, round(4726.577 - max(distance), 1) AS distanceToGo, max(distance) AS distanceCovered, 
       ToDateTime(1000 / (max(distance) / max(rawTime)) * 1000, 'mm:ss') AS pacePerKm,
       ToDateTime(max(rawTime) * 1000, 'mm:ss') AS raceTime
from parkrun 
group by competitorId
ORDER BY distanceToGo, rawTime
limit 10

I have

lat

and

lon

columns. How would I return the corresponding lat/long values for the max rawTime/distance rows?

Rong R

01/25/2022, 5:05 PM

you can't return multiple row for a single group-by key.

Open in Slack

Previous Next