0

I have a table with fields:

id category_id property_id Weight

How can I get list with {category_id, property_id} where property_id from the highest weight?

Example:

id | category_id | property_id | weight |
 1 |           1 |           1 |     20 |
 2 |           1 |           2 |     10 |
 3 |           2 |           2 |     30 |
 4 |           2 |           3 |     40 |

Right results after query:

category_id | property_id
          1 |           1 (because 20 > 10)
          2 |           3 (because 40 > 30)

It is simple issue, but I am searching easiest and right way, how I can do it with postgresql instruments without subqueries and temporary tables.

Yegor Lukash
  • 510
  • 2
  • 18

1 Answers1

2

Use distinct on:

select distinct on (category_id) t.*
from tablewithfields t
order by category_id, weight desc;

EDIT:

You can do this with window functions, but the above is probably more efficient:

select t.*
from (select t.*, row_number() over (partition by category_id order by weight desc) as seqnum
      from tablewithfields t
     ) t
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This syntax is much simpler than `over () partition by...`, and for some reason this is the first time I've ever seen it. Pretty cool. – ngreen Dec 22 '14 at 14:32
  • @ngreen . . . This is Postgres-specific syntax that proves to be quite handy, if you are using that database. – Gordon Linoff Dec 22 '14 at 14:44
  • Good choice, but can I see resolve with windows-functions? – Yegor Lukash Dec 22 '14 at 14:57
  • @GordonLinoff, yes, the syntax is very handy. The SQL syntax is painful and I always have a hard time mapping my mental model of things when using it. – ngreen Dec 29 '14 at 05:06