Question 160589 in PostgreSQL Question posted in
The official documentation can be found here.

What is the max value of each entry from a Postgresql subquery?

I have SQL table with 3 columns – product, product version, and results(number).
I want to create a query that return the max result to each uniq version.

Simple data :

product version results
X 1 50
X 1 51
X 1 53
X 2 60
X 2 62
X 3 70
X 4 80
X 4 80
X 4 80
Y 1 50
Y 2 60
Y 3 70
Y 4 80

In this table, the return value for product X version 1 will be 53, product X version 2 – 62, product X version 3 – 70 and so on.

how can i get this query ?

Tags:

2

Answers


  1. 0

    You can do it using the window function row_number() to sort data in descending order, then row with rn = 1 will be the highest :

    with cte as (
  select *, row_number() over (partition by product, Version order by Results desc) as rn
  from mytable
)
select product, Version, Results
from cte
where rn = 1;

    Demo here

    Login or Signup to reply.
  2. 0

    Distinct on can usually be the answer to "top one per group" type of problems: demo

    select distinct on (product,version) * --specify groups in parentheses
from my_table
order by product,version,--initial `order by` columns need to match `distinct on`
         results desc; --this now dictates what ends up as the top row per group
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search