-1

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 ?

SelVazi
  • 10,028
  • 2
  • 13
  • 29
user12755014
  • 25
  • 1
  • 5
  • Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) – philipxy May 08 '23 at 14:45
  • (Clearly,) This is a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy May 08 '23 at 22:54

2 Answers2

-1

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

SelVazi
  • 10,028
  • 2
  • 13
  • 29
-1

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
Zegarek
  • 6,424
  • 1
  • 13
  • 24