CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Josh B
Josh B

Reputation: 324

Alternative to FIRST_VALUE

I'd like to run this query:

;WITH ToUpdate AS (
   SELECT Id1, Id2,
          FIRST_VALUE(Id1) OVER (PARTITION BY Id2 ORDER BY Id1) AS newValue
   FROM mytable
)
UPDATE ToUpdate
SET Id2 = newValue

But FIRST_VALUE isn't available in SQL Server 2008, is there an alternative?

Upvotes: 1

Views: 952

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : Using OUTER APPLY

;WITH ToUpdate AS (
      SELECT Id1, Id2,
      M.newValue AS newValue
      FROM mytable t1
      OUTER APPLY(
         SELECT top 1 t2.ID1 AS newValue
         FROM mytable t2 WHERE t1.Id2=t2.Id2
         ORDER BY t2.ID1
      )M   
)
UPDATE ToUpdate
SET Id2 = newValue

Upvotes: 2

Related Questions