STRING_AGG top n vs top n % giving different results

Paladugu, Harish 20 Reputation points
2023-11-23T15:56:05.54+00:00
		-- When Running below the top 100 percent vs top 100 STRING_AGG is producing different 
		-- results
		if object_id('tempdb..#tabs') is not null drop table #tabs
		create table #tabs (drrt nchar(2),drky nchar(10),drdl01 nchar(30),seq bigint)
		insert into #tabs 
		select 'S1','ELEVATION ','Exterior                      ',3
		union
		select 'S1','LOT       ','Home Site                     ',1
		union
		select 'S1','PLAN      ','Home Design                   ',2
		union
		select 'S1','SUMMARY   ','Summary                       ',99
		union
		select 'SL','BASEMENT  ','Basement                      ',17
		union
		select 'SL','BATH      ','Bathroom                      ',16
		union
		select 'SL','KITCHEN   ','Kitchen/Appliances            ',15
		union
		select 'SL','LOOK      ','Look                          ',11
		union
		select 'SL','OPTIONS   ','Options                       ',18
		union
		select 'SL','PALETTE   ','Palette                       ',13
		--
		select string_agg(tabs,',') from (
		select top 100 percent tabs='{"ky":"'+ rtrim(drky) + '",' + '"dispval":"' + rtrim(drdl01) + 
		'",' +'"seq":"' + format(seq,'#########0') +
		'"}'
		from #tabs  order by SEQ) tb
		--
		select string_agg(tabs,',') from (
		select top 100  tabs='{"ky":"'+ rtrim(drky) + '",' + '"dispval":"' + rtrim(drdl01) + 
		'",' +'"seq":"' + format(seq,'#########0') +
		'"}'
		from #tabs  order by SEQ) tb

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,273 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.4K Reputation points
    2023-11-23T16:18:27.7033333+00:00

    Try to order the data:

    select string_agg(tabs, ',') within group ( order by seq ) from (
    select top 100 percent tabs='{"ky":"'+ rtrim(drky) + '",' + '"dispval":"' + rtrim(drdl01) + 
    '",' +'"seq":"' + format(seq,'#########0') +
    '"}', seq
    from #tabs  order by SEQ) tb
    --
    select string_agg(tabs, ',') within group ( order by seq ) from (
    select top 100 tabs='{"ky":"'+ rtrim(drky) + '",' + '"dispval":"' + rtrim(drdl01) + 
    '",' +'"seq":"' + format(seq,'#########0') +
    '"}', seq
    from #tabs  order by SEQ) tb
    

    Note that top 100 is not top 100 percent.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful