[SOLVED] Postgres - Get recent updated row for each hospital names

Postgres - Get recent updated row for each hospital names

I want to get the latest updated row of each hospital. I am writing the query like below:

query = f"""
        SELECT to_json(r)
        FROM (
            SELECT LatestRecords.hospitalName AS "hospitalName", lastUpdatedOn AS "lastUpdatedOn", LatestRecords.active AS "active"
            FROM (
                SELECT hospitalName AS "hospitalName", lastUpdatedOn AS "lastUpdatedOn", active AS "active", ROW_NUMBER() OVER (PARTITION BY facilityName ORDER BY lastUpdatedOn DESC) AS rn
                FROM hospitals
            ) AS LatestRecords
            WHERE rn = 1
        ) AS r;
    """

but always failing:

column latestrecords.hospitalName does not exist,LINE 4: SELECT LatestRecords.hospitalName AS "hospitalName", Lates..., ^,HINT: Perhaps you meant to reference the column "latestrecords.hospitalName".,"

Please help, thanks in advance!

I want to get the latest updated objects for each hospital.

Solution

Postgresql is a bit picky about names, when you use double quotes in names, you need to use the also in the select So "hospitalName" must be in the SELECT also be "hospitalName" with double quotes, without them it is a different column

query = f"""
        SELECT to_json(r)
        FROM (
            SELECT LatestRecords."hospitalName" AS "hospitalName", "lastUpdatedOn" AS "lastUpdatedOn", LatestRecords."active" AS "active"
            FROM (
                SELECT hospitalName AS "hospitalName", lastUpdatedOn AS "lastUpdatedOn", active AS "active", ROW_NUMBER() OVER (PARTITION BY facilityName ORDER BY lastUpdatedOn DESC) AS rn
                FROM hospitals
            ) AS LatestRecords
            WHERE rn = 1
        ) AS r;
    """