Try to duplicate column= attribute1 at time=0 horizontally even on rows where time!=0 for every unique ID

Question

Given a table such as this,

I want to create a table via SQL without doing window functions

id	time	attribute1	attribute1atFirstRecordedTimeperID
1	0	a	a
1	2	b	a
2	0	c	c
2	4	d	c

I tried

SELECT 
    t1.id, 
    t1.time, 
    t1.attribute1,
    attribute1fixedattime0 = t2.attribute1
FROM s t1
INNER JOIN (
    SELECT attribute1 AS attribute1fixedattime0
    FROM s
    WHERE time = 0
) t2 ON t1.id = t2.id

Is the column alias correct?

That throws an error

If I fix this code, I want to make the SQL code work for the case when id=2 does not have their first attribute1 recorded at time t=0, but for t>0. Then do I do a min on the join condition or inside the JOIN?

I want to create a table via SQL without doing window functions to turn

into

To go from

id	time	attribute1
1	0	a
1	2	b
2	42	c
2	69	d

to the table

id	time	attribute1	attribute1atFirstRecordedTimeperID
1	0	a	a
1	2	b	a
2	42	c	c
2	69	d	c

if you use a row_number() window function to mark the spot at which first time is within every id, then how do you join or would you not use a join but something else?

SELECT 
    id,
    time,
    attribute1,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY time) AS rn
FROM your_table
SELECT
    id,
    time,
    attribute1,
    FIRST_VALUE(attribute1) OVER (PARTITION BY id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS attribute1atFirstRecordedTimeperID
FROM (
    -- Previous query here
) AS subquery

Olivier Jacot-Descombes · Accepted Answer · 2024-01-25T16:40:37.150

The problem is that you are trying to access a column attibute1 from t2, but you gave this column the alias attribute1fixedattime0. So, use this alias:

SELECT 
    t1.id, 
    t1.time, 
    t1.attribute1,
    t2.attribute1fixedattime0
FROM s t1
...

But I don't see the reason for this sub-select. You could join the table directly:

SELECT
    t1.id, 
    t1.time, 
    t1.attribute1,
    t2.attribute1 AS attribute1fixedattime0
FROM
    s t1
    INNER JOIN s t2
        ON t1.id = t2.id
WHERE t2.time = 0

However, this doesn't solve the problem. What you need is the minimum time value per id. Therefore create a SELECT with a GROUP BY:

SELECT id, MIN(time) AS time0
FROM s
GROUP BY id

We could also select MIN(attribute1); however, I am not sure whether the attribute associated with the minimum time is also the minimum attribute. But we can get this attribute by doing another join:

SELECT tm.id, tm.time0, ta.attribute1 AS attr0
FROM
    ( SELECT id, MIN(time) AS time0
      FROM s
      GROUP BY id) tm
    INNER JOIN s ta
        ON tm.id = ta.id AND tm.time0 = ta.time

Now, we have the minimum time and its attribute per id and can do the final join to the original table:

SELECT
    t1.id,
    IIf(t1.time = t2.time0, 0, t1.time) AS [time],
    t1.attribute1,
    t2.attr0 AS attribute1fixedattime0
FROM
    s t1
    INNER JOIN (
        SELECT tm.id, tm.time0, ta.attribute1 AS attr0
        FROM
            ( SELECT id, MIN(time) AS time0
              FROM s
              GROUP BY id) tm
            INNER JOIN s ta
                ON tm.id = ta.id AND tm.time0 = ta.time
    ) t2
    ON t1.id = t2.id

Note that we use Iff to check for the row with the minimum time and then set the time to 0 even if it is not. I also put [time] inbrackets because it is a reserved word.

Try to duplicate column= attribute1 at time=0 horizontally even on rows where time!=0 for every unique ID

1 Answers1