November 19, 2013 at 3:05 pm
I am looking for a way to update a column for only one row for each ID based on a predefined ranking order.
I have a table with multiple rows per ID, each row will have position column, a column with the ranked values and a flag. the flag should only be set for one row per ID and for the row that has the highest ranking. This is what I have come up with that achieves the result but I am wondering if there is a more concise way getting the same result.
create table #tmp (ID int, pos int, ltr char(1),flag char(1))
insert into #tmp(ID,pos,ltr)
values(1,1,'A'),(1,2,'B'),(2,1,'A'),(3,1,'B'),(3,2,'C'),(3,3,'A'),(4,1,'B'),(5,1,'C'),(5,2,'B'),(6,1,'C'),(7,1,'D'),(7,2,'B'),(7,3,'C'),(7,4,'A')
with cte as(
select ID,
CASE WHEN ltr = 'A' then 1
WHEN ltr = 'B' then 2
WHEN ltr = 'C' then 3
WHEN ltr = 'D' then 4
END AS rank_type,
ltr,
POS
from #tmp),
min_rank as (select ID,min(rank_type) min_rank
from cte
group by ID),
updt as(
select a.ID,a.pos
from cte a
inner join min_rank b
on a.ID = b.ID
and a.rank_type = b.min_rank)
update t
set flag = 'Y'
from #tmp t
inner join updt u
on t.ID = u.ID
and t.pos = u.pos
SELECT *
FROM #tmp
drop table #tmp
November 19, 2013 at 3:24 pm
Here's a more concise way to do it. You can update the table by updating the CTE.;-)
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ltr) rn
FROM #tmp
)
UPDATE CTE SET
flag = 'Y'
WHERE rn = 1;
If your rank is not ordered alphabetically, you can simply change the column with the CASE statement in the ORDER BY clause
November 20, 2013 at 9:05 am
November 20, 2013 at 9:18 am
You're welcome. I was gladly surprised the first time I realized that I could update/delete a CTE (is mostly like views).
November 21, 2013 at 11:06 am
Of course not all CTEs are going to be updateable, it will depend on the specific coding of the CTE ;-).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 22, 2013 at 10:42 am
Luis Cazares (11/19/2013)
Here's a more concise way to do it. You can update the table by updating the CTE.;-)
Put this on the list of things I'm mad I didn't know.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply