0

Given a table such as this,

enter image description here

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 errorenter image description here

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

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.