Use GROUP_BY with extra columns in postgreSQL

Question

Let's suppose we have a simple table users:

id  | name | company |  other columns.......
----+------+---------+-------------------------
 1  | A    | A1
 2  | A    | A2
 3  | B    | B1
 4  | C    | C1
 5  | C    | C2
 6  | C    | C3
 ....

I want to group by name, and chose the latest value for id and company. The result I expect is three column table:

id  | name | company |
----+------+---------+
 2  | A    | A2
 3  | B    | B1
 6  | C    | C3
 ....

I am trying to use GROUP_BY, but don't know how to include company column:

SELECT
  max(id),
  name,
  ? # I don't know how to include company
FROM users
GROUP_BY name

Does anyone have better idea?

Gordon Linoff · Accepted Answer · 2020-10-19 10:58:36Z

5

Use distinct on:

select distinct on (name) u.*
from users u
order by name, id desc;

distinct on is a very convenient Postgres extension. It returns the first row from a group of rows. The "grouping" is based on the columns after distinct on. The ordering is based on the order by clause.

There are two other common ways to address this. One method uses window functions:

select u.*
from (select u.*,
             row_number() over (partition by name order by id desc) as seqnum
      from users u
     ) u
where seqnum = 1;

Or a correlated subquery:

select u.*
from users u
where u.id = (select max(u2.id) from users u2 where u2.name -= u.name);

There is even a "clever" way of doing this using group by. Postgres doesn't have a "first" or "last" aggregation function. But you can use arrays:

select name, max(id),
       (array_agg(company order by id desc))[1] as country
from users u
group by name;

answered Oct 19, 2020 at 10:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Over a year ago

Incredible answer! Thanks.

Collectives™ on Stack Overflow

Use GROUP_BY with extra columns in postgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related