OVER clause for VARCHAR-CodePudding

I can use over clause for numeric and date columns using an aggregate function. But, I'm stuck with being unable to use over clause for the varchar column. In the example below, I can reproduce the FIRST_FILL_DT column using the following lines:

MIN(FILL_DATE) OVER(PARTITION BY ID) AS FIRST_FILL_DT

However, when trying to produce the FIRST_BP_MED column, I am not sure if I can use similar syntax because I don't know if the aggregate function works correctly with VARCHAR Columns.

Can anyone please offer insights or guidance on how to solve this?

My data is like this:

My desired data should like this:

CodePudding user response：

If your database supports the FIRST_VALUE window function, you can use something like this:

FIRST_VALUE(BP_MED) OVER (PARTITION BY ID ORDER BY FILL_DATE) AS first_bp_med

Docs for FIRST_VALUE:

MySQL, SQL Server, Postgresql, SQLite

CodePudding user response：

This is pretty straight forward. Use 'FIRST_VALUE' over your window clause to pick the first value omitted by your partition irrespective of the condition.

https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver15

SELECT 
    ID, FILL_DATE, BP_MED, 
    MIN (FILL_DATE) OVER (PARTITION BY ID ORDER BY FILL_DATE) AS FIRST_FILL_DT, 
    FIRST_VALUE (BP_MED) OVER (PARTITION BY ID ORDER BY FILL_DATE) AS FIRST_BP_MED
FROM 
    YOURTABLE;