PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres query for applying groupBy to the top X rows based on created_on timestamp column?

I have the following table in Postgres:

adjustments table

  • id (pk)
  • property_id
  • value
  • created_on_utc

I would like to create a query that will get the top X most recent records based on the created_on_utc column and then group and count the number of records for each property_id.

I tried the statement below, but I get an error:

SELECT COUNT(a.id), property_id FROM adjustments as a GROUP BY a.property_id ORDER BY created_on_utc DESC; LIMIT 1000;

Error:
SQL Error [42803]: ERROR: column "pa.created_at" must appear in the GROUP BY clause or be used in an aggregate function
Position: 87

Tags:

2

Answers


  1. 0

    You can use either of following scripts.

    With using CTE:

    WITH recent_adjustments AS (
    SELECT id, property_id
    FROM adjustments
    ORDER BY created_on_utc DESC
    LIMIT 1000
)
SELECT 
    property_id, 
    COUNT(id) AS record_count
FROM 
    recent_adjustments
GROUP BY 
    property_id;

    Without using CTE:

    SELECT 
    property_id, 
    COUNT(id) AS record_count
FROM 
    (SELECT id, property_id
     FROM adjustments
     ORDER BY created_on_utc DESC
     LIMIT 1000) AS recent_adjustments
GROUP BY 
    property_id;
    Login or Signup to reply.
  2. 0

    The error is due to the fact that you are attempting to order the results by a column that is not included in the GROUP BY clause or used in an aggregate function.

    In your case, you want to first select the top X most recent records, and then perform a count based on property_id. You can achieve this by using a subquery as follows:

    SELECT COUNT(id), property_id 
FROM (
    SELECT id, property_id 
    FROM adjustments 
    ORDER BY created_on_utc DESC 
    LIMIT 1000
) GROUP BY property_id;

    In the subquery, we select the top 1000 most recent records. Then, in the outer query, we count the number of records for each property_id based on this result

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search