CODEWITHSUNDEEP
sqldatabasepostgresqlgroup-bydistinct-on
Koen
Koen

Reputation: 55

PostgreSQL distinct on, group by, having in one?

I'm am having some trouble with a PostgreSQL query

What I want is a list of "Campaigns", ordered by distance, and within a minimum distance from a location [in_lat, in_lng, in_radius] Now, I am retrieving the distance (with distance_in_km(...) ) twice. Once for ordering, once for checking if the campaign is within my preferred radius.

Also, It's possible that a campaign has multiple "Campaignreceiver" entries (receivetype [EMAIL, WEBSITE, etc...])

And I want to retrieve the campaign just once. hence the DISTINCT ON.

So the question is, how can I get sometime like "WHERE distance < in_radius" to work. My guess is adding a GROUP BY and HAVING but I did some tests and didn't get is to work.

ANY HELP IS VERY MUCH APPRECIATED!!!

    SELECT DISTINCT ON (c.campaign_uuid, distance)
            c.campaign_uuid,
            prl.lat,
            prl.long,
            distance_in_km(prl.lat, prl.long, in_lat, in_lng) AS distance                
        FROM 
            usercampaignrelations AS ucr
        LEFT JOIN
            campaignreceivers AS cr
        ON
            ucr.usercampaign_uuid = cr.usercampaign_uuid
        LEFT JOIN
            campaigntargetgrouprelations AS ctg
        ON
            cr.campaigntargetgroup_uuid = ctg.campaigntargetgroup_uuid
        LEFT JOIN
            campaigns AS c
        ON
            ucr.campaign_uuid = c.campaign_uuid
        LEFT JOIN
            companycampaignrelations AS cc
        ON
            c.campaign_uuid = cc.campaign_uuid
        LEFT JOIN
            pointradiuslocations AS prl
        ON
            c.location_uuid = prl.location_uuid
        WHERE 
            ucr.user_uuid = in_user_uuid
        AND
            (cr.status = 'SENT' OR cr.status = 'RETRIEVED')
        AND
            distance_in_km(prl.lat, prl.long, in_latitude, in_longtitude) < in_radius
        ORDER BY
            distance
        LIMIT 
            in_limit

Upvotes: 1

Views: 1184

Answers (1)

aldem
aldem

Reputation: 191

AFAIK, distance_in_km() will be called only once (per row), as long as you use it with the same arguments, thus WHERE and ORDER BY will not evaluate it again.

Upvotes: 1

Related Questions