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

Can Table A’s Grouped Results in PostgreSQL be sorted 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;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    To group records by res_id and sort them based on the maximum (max) value of the latest_update field within each group, you can include the max(latest_update) function in your select query. This allows you to first group the records by res_id and then select the maximum (max) date for each group. Finally, you can sort the entire result set based on this maximum date.

    Here's an example of how you can construct the query:

    SELECT res_id, max(latest_update) AS max_date
FROM table_A
GROUP BY res_id
ORDER BY max_date;

  2. 0

    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.

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

Back To Top
Search