StealthRT StealthRT -4 years ago 94
SQL Question

SQL order by highest to lowest in one table referencing another table in an UPDATE

Hey all I have the following tables that need in order to get data from one that matches the other and have it from highest to lowest depending on the int of TempVersion.

UPDATE
net_Users
SET
net_Users.DefaultId = b.TId
FROM
(SELECT
TOP 1 IndivId,
TId
FROM
UTeams
WHERE
UTeams.[Active] = 1
ORDER BY
TempVersion DESC
) AS b
WHERE
net_Users.IndivId = b.IndivId


In the above I am trying to order from the highest TempVersion to the lowest.

The query above seems to just update 1 of those records with the TempVersion and stop there. I am needing it to loop to find all associated users with the same IndivId matching.

Anyone able to help me out with this?

sample data

net_Users:

name | DefaultId | IndivId | etc...
--------+-----------+---------+-------
Bob | | 87 | etc...
Jan | | 231 | etc...
Luke | | 8 | etc...


UTeams:

IndivId | TempVersion | etc...
--------+-------------+-------
8 | 44 | etc...
17 | 18 | etc...
8 | 51 | etc...
8 | 2 | etc...
7 | 22 | etc...
8 | 125 | etc...
87 | 10 | etc...
14 | 88 | etc...
8 | 5 | etc...
15 | 54 | etc...
65 | 11 | etc...
87 | 15 | etc...
39 | 104 | etc...


And the output I would be needing is (going to choose IndivId 8):

In net_users:

Name | DefaultId | IndivId | etc...
-----+-----------+---------+-------
Luke | 125 | 8 | etc...
Luke | 51 | 8 | etc...
Luke | 44 | 8 | etc...
Luke | 5 | 8 | etc...
Luke | 2 | 8 | etc...

Answer Source

I think this is what you were trying to do:

update net_Users
  set net_Users.DefaultId = coalesce((
    select top 1 TId
    from UTeams
    where UTeams.[Active] = 1
      and net_Users.IndivId = UTeams.IndivId
    order by u.TempVersion desc
    )
    ,net_Users.DefaultId
    )

another way using cross apply()

update n
  set DefaultId = coalesce(x.Tid,n.DefaultId)
from net_Users as n
cross apply (
  select top 1 TId
  from UTeams as u
  where u.[Active] = 1
    and n.IndivId = u.IndivId
  order by u.TempVersion desc
) as x

another way to do that with a common table expression and row_number()

with cte as (
  select 
    n.IndivId
  , n.DefaultId
  , u.Tid
  , rn = row_number() over (
      partition by n.IndivId 
      order by TempVersion desc
      )
  from net_users as n
    inner join UTeams as u
      on n.IndivId = u.IndivId
  where u.[Active]=1
)
update cte
set DefaultId = Tid
where rn = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download