Geek Answers Handbook

Postgresql: how to get primary key from group by clause?

This is a query that selects the set of required rows:

select max(a), b, c, d, e
from T
group by b, c, d, e;

The table has a primary key in the column id.

I would like to identify these rows in the next query, getting the primary key from each of these rows. How should I do it? This does not work:

select id, max(a), b, c, d, e
from T 
group by b, c, d, e;

ERROR:  column "T.id" must appear in the GROUP BY clause or be used in an aggregate function

I tried this from the fact that I poked around some other postgresql questions, but no luck:

select distinct on (id) id, max(a), b, c, d, e
from T 
group by b, c, d, e;

ERROR:  column "T.id" must appear in the GROUP BY clause or be used in an aggregate function

What should I do? I know that for each result there can be only one id, call its primary key ... I literally want the primary key along with the rest of the data for each row to return the original (working) request.

+5
source share
5

, id , id , id. max min :

-- Or min(id) if you want better spiritual balance.
select max(id), max(a), b, c, d, e
from T 
group by b, c, d, e;

, ( ):

select id, a, b, c, d, e
from (
    select id, a, b, c, d, e, rank() over (partition by b,c,d,e order by a desc) as r
    from T
) as dt
where r = 1
+4

, , (, ,) (, id) .

PostgreSQL - , .

  • b,c,d,e
  • array_agg id .

: Postgresql GROUP_CONCAT ?

№ 3 .

, . !

+3

, , .

SELECT T.id, T.a, T.b, T.c, T.d, T.e
    FROM (SELECT max(a) AS MaxA, b, c, d, e
              FROM T
              GROUP BY b,c,d,e) q
        INNER JOIN T
            ON T.a = q.MaxA
                AND T.b = q.b
                AND T.c = q.c
                AND T.d = q.d
                AND T.e = q.e
+1

, , , , , . , , ​​// .. .

The best way I found for this is using the view to find all the maximum values ​​for groups. If your source table

create table T as (
  id integer primary key,
  a integer,
  b integer,
  c integer,
  d integer)

then create the max view as

create view T_maxgroups as 
  select max(a) as a, b, c, d 
  from T
  group by b, c, d

(this is your initial query) and then attach this view to your table to get the rows with maximum values:

select T.* from T join maxgroups using (a,b,c,d)
+1
source

juuust is curiously adding him to the work group?

select T.id, max(a), b, c, d, e
from T 
group by T.id b, c, d, e;
0
source

More articles:


All Articles