Get the data with the maximum legth

Transact-SQL
1

hello,

i have a table in which i need to update the column "comments" based on the maximum legth by ID.
i need that in column "Comments" to write the maximul legth coment by ID.
in table 1 is the intiial data and table 2 has the data as i want to have:

image
image.png784×279 6.33 KB

i tried to use max(length(Comments)) or max(datalength(Comments)) but it gives me the number length instead of the comments itself. I also tried max(comments) but it gives me the forst commet that it sees for the speciffic ID.

maybe someone can give me an idea.
thank you :slight_smile:

2

Use max with over(partition by ID ...

3

hi,

i already tried this
select max([Comments]) over (partition by[ID]) from [dbo].#table_temp1
where [ID]='OPE-0006117509'

and it gives me "Quote audit pending (check contribution):Valid quote exists. "
instead of "Quote audit passed:Valid quote exists. Hunter PPO before Quote start date. Hunter had previous opp."

4

Why would you want to overwrite values and create duplicates? Why not simply delete the "short" rows?

5 
declare @source table
( id int not null
, comments varchar(200) not null)

insert into @source(id,comments)
values(1,'Quote audit pending (check contribution):Valid quote exists. ')
,(1,'Quote audit passed Valid quote exists. Hunter PPO before Quote start date. Hunter had previous opp.')



select
 id
 ,MAX(case when len(comments) = maxLen then comments else '' end ) OVER(partition by id order by id
                                     Rows between Unbounded preceding and unbounded following) as newComment
from
(
	select
	 id
	 ,comments
	 ,max(len(comments)) OVER(partition by id) as maxLen
	from @source)A
6

because i have other columns also in this table with different data per row. Here i simplified the example by showing only the columns that i need for this update :slight_smile:

7

thank you it worked :smiley:

8

Super! my pleasure!