Post reply

Getting MAX and MIN values for all values in a field

  • October 12, 2022 at 9:25 am

    #4098316

    I have such a situation. I am trying to get MIN and MAX values of one field for all rows.

    MIN (WEEK_P) should be always one value 20220917 for all fields all the time. Now the Window Function doesn't work for some reason. Is there anything I am doing wrong?

     

    SELECT FNL_DC AS 'DC', FNL_MC AS 'BUDGET_CHAIN', FNL_MODE AS 'MODE', FNL_WEEK
     , MIN(WEEK_P) OVER (PARTITION BY WEEK_P ORDER BY WEEK_P) AS MIN_WEEK_P  -- trying to get MIN value of all the values (ex. 20220917) -- right now I have 20220924, 20221001, 20220917, NULL
     , MAX(DEWEDT) OVER (PARTITION BY DEWEDT ORDER BY DEWEDT) AS MAX_DEWEDT -- trying to get MAX value for all the values (ex. 20221008) -- right now I have 20221008, 20220917, NULL

     

    Let me know.

     

  • October 12, 2022 at 10:05 am

    #4098366

    You are partioning on the same column that you do MIN/MAX on. This will not work. As I understand your requirements, you shouldn't do a PARTITION BY, since you want the MAX/MAX to cover all rows in your SELECT. However, since the PARTION BY is mandatory in the window function, you still need to provide a dummy value. In such cases just choose a constant value.

    SELECT FNL_DC AS 'DC', FNL_MC AS 'BUDGET_CHAIN', FNL_MODE AS 'MODE', FNL_WEEK
     , MIN(WEEK_P) OVER (PARTITION BY '') AS MIN_WEEK_P  -- trying to get MIN value of all the values (ex. 20220917) -- right now I have 20220924, 20221001, 20220917, NULL
     , MAX(DEWEDT) OVER (PARTITION BY '') AS MAX_DEWEDT -- trying to get MAX value for all the values (ex. 20221008) -- right now I have 20221008, 20220917, NULL

    • This reply was modified 2 years, 2 months ago by  kaj. Reason: Removed ORDER BY in the window function, since it's not necessary

  • October 12, 2022 at 1:53 pm

    #4098468

    kaj wrote:

    You are partioning on the same column that you do MIN/MAX on. This will not work. As I understand your requirements, you shouldn't do a PARTITION BY, since you want the MAX/MAX to cover all rows in your SELECT. However, since the PARTION BY is mandatory in the window function, you still need to provide a dummy value. In such cases just choose a constant value.

    SELECT FNL_DC AS 'DC', FNL_MC AS 'BUDGET_CHAIN', FNL_MODE AS 'MODE', FNL_WEEK
     , MIN(WEEK_P) OVER (PARTITION BY '') AS MIN_WEEK_P  -- trying to get MIN value of all the values (ex. 20220917) -- right now I have 20220924, 20221001, 20220917, NULL
     , MAX(DEWEDT) OVER (PARTITION BY '') AS MAX_DEWEDT -- trying to get MAX value for all the values (ex. 20221008) -- right now I have 20221008, 20220917, NULL

    The PARTITION BY is NOT required.

    SELECT FNL_DC AS 'DC', FNL_MC AS 'BUDGET_CHAIN', FNL_MODE AS 'MODE', FNL_WEEK
     , MIN(WEEK_P) OVER () AS MIN_WEEK_P  -- trying to get MIN value of all the values (ex. 20220917) -- right now I have 20220924, 20221001, 20220917, NULL
     , MAX(DEWEDT) OVER () AS MAX_DEWEDT -- trying to get MAX value for all the values (ex. 20221008) -- right now I have 20221008, 20220917, NULL

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • October 12, 2022 at 2:17 pm

    #4098518

    Kaj Thank you very much. It works. Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply