SQLTeam.com | Weblogs | Forums

Get the data with the maximum legth

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:

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:

Use max with over(partition by ID ...

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."

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

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

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:

thank you it worked :smiley:

Super! my pleasure!