Group by but no select

Question

I know there are tons of Topics like this. But I can not find a solution for this. Maybe there is an other way than Group.

I have this query

Select id1, id2, share from table1

Result:

| id1 | id2 | share | +-------+------+------------+ | 3864 | 3083 | 0.157223 | | 3864 | 3095 | 0.007548 | | 57695 | 3095 | 1 | | 57749 | 2864 | 0.99516 |

I want the highest share grouped by id1 without losing the id2

So it should look like this:

| id1 | id2 | share | +-------+------+------------+ | 3864 | 3083 | 0.157223 | | 57695 | 3095 | 1 | | 57749 | 2864 | 0.99516 |

I could do this: group only by id1 and do a join on the old table by id1 and the share to get the id2.

But there must be a better way?

stackoverflow.com/questions/tagged/…

user330315
– user330315

2020-01-10 10:04:31 +00:00
Commented Jan 10, 2020 at 10:04 — user330315
– user330315, Commented Jan 10, 2020 at 10:04

user330315 · Accepted Answer · 2020-01-10 10:05:28Z

Queries for greatest-n-per-group are typically most efficient using distinct on() in Postgres

select distinct on (id) * from the_table order by id, share desc;

Yogesh Sharma · Accepted Answer · 2020-01-10 10:07:41Z

Use row_number() :

select t.* from (select t.*, row_number() over (partition by t.id1 order by t.share desc) as seq from table t ) t where seq = 1;

In PostgreSQL you can use distinct on :

select distinct on (t.id) t.* from table t order by t.id, share desc;

Fahmi · Accepted Answer · 2020-01-10 10:04:04Z

Use row_number()

select * from ( select *, row_number() over(partition by id1 order by id2) as rn from tablename )A where rn=1

Collectives™ on Stack Overflow

Group by but no select

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related