CODEWITHSUNDEEP
postgresqljoingroup-by
91DarioDev
91DarioDev

Reputation: 1700

postgres: join, group by, order by. Is my query wrong?

my tables

CREATE TABLE IF NOT EXISTS members(
    group_id BIGINT, 
    amount INT, 
    updated_date timestamp)

CREATE TABLE IF NOT EXISTS supergroups_ref(
    group_id BIGINT PRIMARY KEY, 
    title TEXT DEFAULT NULL, 
    message_date timestamp)

CREATE TABLE IF NOT EXISTS supergroups(
    group_id BIGINT PRIMARY KEY, 
    lang TEXT DEFAULT NULL, 
    last_date timestamp DEFAULT NULL)

example of data in members

(3, 90, date)
(1, 15, date)
(2, 200, date)
(1, 28, date)
(2, 300, date)
(1, 80, date)

example of data in supergroups_ref:

(1, 'title', date)
(2, 'title2', date)
(3, 'title3', date)

example of data in supergroups:

(1, 'en', date)
(2, 'it', date)
(3, 'it', date)

I would like to able to do a query that from the table members take only the most recent rows ordering them by 'updated_date' DESC and left join them on supergroups, so that every group_id in supergroups has the right most recent amount of members. Now i would like to append with another left join the title info in supergroups_ref

and i would like to order them by the amount of members table.

i tried to do this but i not sure that i am doing it right:

SELECT sub.group_id, sub.amount, sub.title
FROM (
    SELECT s.group_id, m.amount, s_ref.title
    FROM  supergroups AS s
    LEFT OUTER JOIN members AS m
    ON m.group_id = s.group_id
    LEFT OUTER JOIN supergroups_ref AS s_ref
    ON s_ref.group_id = s.group_id
    ORDER BY m.updated_date DESC
) AS sub
GROUP BY sub.group_id, sub.amount, sub.title
ORDER BY sub.amount DESC

i am afraid that:

  1. i am doing something wrong in the query
  2. i complicated the query more than necessary

desired result will be: [(2, 200, 'title2'), (3, 90, 'title3'), (1, 15, 'title1')] the second element of each element is most recent amount of members since along with the amount is stored the date too in the members table. And the tuple is ordered by that

Upvotes: 1

Views: 681

Answers (1)

Dan
Dan

Reputation: 1891

If I got you clearly, this is what you need:

SELECT members.*,supergroups.lang,supergroups_ref.title
FROM
-- Window function to get only de last_date:
    (SELECT last_members.group_id,last_members.amount
    FROM
     (SELECT *,row_number() OVER (PARTITION BY group_id
     ORDER BY updated_date DESC) as row FROM members)last_members
    WHERE last_members.row=1)members
-- Joins with other tables
LEFT JOIN supergroups ON members.group_id=supergroups.group_id
LEFT JOIN supergroups_ref ON supergroups.group_id=supergroups_ref.group_id

This should return:

group_id |  amount  |   lang  | title
--------------------------------------
1        |  15      |   en    | title
2        |  200     |   it    | title2
3        |  90      |   it    | title3

For this query to work you need to use window functions, here is a nice article about them Understanding Window Functions

Upvotes: 1

Related Questions