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

Window function POSTGRESQL

YEAR sales
2021 1000
2022 1500
2023 2000

I want show 3rd column min sales with window function. But year=2022.

YEAR sales min
2021 1000 1500
2022 1500 1500
2023 2000 1500

I’ve tried the following query, but it didn’t return the desired result:

select *,min(sales) over (partiton by year=2022) from table
Tags:

2

Answers


  1. 0

    It doesn’t have anything to do with window functions:

    select *
from "table",
(select min(sales) as minSale from "table" where year = 2022) x;
    Login or Signup to reply.
  2. 0

    When correcting the type in partiton to partition, you will get:

    select 
   *,
   min(sales) over (partition by year=2022) 
from mytable

    The results will be (see: DBFIDDLE):

    year sales min
    2021 1000 1000
    2022 1500 1500
    2023 2000 1000

    This means the the sales value (or, actually, the smallest value for sales), in the year 2022 is 1500 (line 2). And that the smallest value for sales in the years that are nor equal to 2022 is 1000 (lines 1 and 3)

    P.S. I also renamed table to mytable, because table is a reserved word. One should avoid using a reserved word in an SQL query, more info: https://stackoverflow.com/search?q=reserved+word+sql+table+

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search