Sorting PostgreSQL Grouped Results in Table A by Last Update Date

I have two PostgreSQL tables, ‘A’ and ‘B’, with the following structures:

Table ‘A’:

  • id: primary key with auto-increment
  • res_id: foreign key referencing table ‘B’
  • description: table description
  • last_update: date type

Table ‘B’:

  • id: integer primary key with auto-increment
  • description: varchar(256)

I need assistance with constructing a query that involves grouping by res_id in table ‘A’ and sorting the results based on the last_update date from table ‘A’. How can I achieve this?

I’ve tried the following query:

SELECT count(id),res_id 
from A GROUP BY res_id
ORDER BY id last_update;

>Solution :

To construct a query that involves grouping by ‘res_id’ in table ‘A’ and sorting the results based on the ‘last_update’ date from table ‘A’, you can use the following SQL query:

SELECT A.res_id, B.description, MAX(A.last_update) AS max_last_update
FROM A
JOIN B ON A.res_id = B.id
GROUP BY A.res_id, B.description
ORDER BY max_last_update DESC;

In this query, we use the JOIN keyword to combine the rows from table ‘A’ with the corresponding rows from table ‘B’ based on the foreign key relationship between ‘res_id’ in table ‘A’ and ‘id’ in table ‘B’.

We then use the GROUP BY clause to group the results by ‘res_id’ and ‘description’. This ensures that we get a single row for each unique combination of ‘res_id’ and ‘description’.

Next, we use the MAX function to select the maximum ‘last_update’ date for each group. This will give us the latest ‘last_update’ date for each ‘res_id’.

Finally, we use the ORDER BY clause to sort the results in descending order based on the maximum ‘last_update’ date (‘max_last_update’).

Make sure to replace ‘A’ and ‘B’ with the actual table names you have in your database.

Leave a Reply