CODEWITHSUNDEEP
sqlpostgresqlspatial-queryearthdistance
Jefferson Sousa
Jefferson Sousa

Reputation: 1

PostgreSQL aggregate field

I have a table called served_location, which have the latitude and longitude of a place, and im using input parameters $latitude and $longitude to calculate the distance between the input and places in database, everything is working fine, im grouping by the user that serve that location, and using earthdistance to bring the distance from the $latitude, $longitude parameters. The problem is: im ordering by the minor distance, now i need to bring the name of that location with the minor distance, but im not able to, as the earthdistance will calculate and bring only the number and i can't use aggregate functions in SELECT FILTER subquery.

I tried the below query:

SELECT
    P.*,
    MIN(SL."name") AS "locationName",
    COUNT(SL."name") AS "serveNear",
    MIN(AC."price") AS "price",
    ROUND(
        MIN(
            EARTH_DISTANCE (
                LL_TO_EARTH ($latitudeParam, $longitudeParam),
                LL_TO_EARTH (LATITUDE, LONGITUDE)
            )
        )::NUMERIC,
        2
    ) AS "distance"
FROM
    "account_service" "ac"
    INNER JOIN "service" "s" ON S."serviceId" = AC."serviceId"
    AND S."serviceEnabled" IS TRUE
    INNER JOIN "account" "a" ON AC."accountId" = a."accountId"
    AND A."status" = 4
    AND A."type" = 2
    INNER JOIN "payment_account" "pay" ON PAY."accountId" = A."accountId"
    INNER JOIN "profile" "p" ON A."accountId" = P."accountId"
    INNER JOIN "served_location" "sl" ON A."accountId" = SL."accountId"
WHERE
    S."serviceType" = '1'
    AND P."profileName" IS NOT NULL
    AND P."mdProfileImage" IS NOT NULL
GROUP BY
    P."profileId"
ORDER BY
    DISTANCE ASC;

The query is working, BUT the "locationName" i have aggregated, is NOT the one with the minor "distance", it is only the field with minor length, so what is i expect is:

The query to return everything equal, but the "name" field to be the one with the minor distance that come from earthdistance.

Upvotes: 0

Views: 26

Answers (0)

Related Questions