sorting - Ampscript: desc sort field values from data extension and pass them to another variable -

i have data extensions 6 fields:

emailadress (type: emailadress, unique) field1 (type: number, not nullable) field2 (type: number, not nullable) field3 (type: number, not nullable) field4 (type: number, not nullable) field5 (type: number, not nullable) 

i have 3 objectives:

1. pass values of each field ampscript variables (the easy part)

%%[ set @var1 = field1 set @var2 = field2 set @var3 = field3 set @var4 = field4 set @var5 = field5 ]%% 

2. sort variables descending based on values , (3) pass them on 1 of 5 contentareas.

%%=contentareabyname("my contents\campaigns\mainitem\@maxvar1")=%% %%=contentareabyname("my contents\campaigns\subitem1\@maxvar2")=%% %%=contentareabyname("my contents\campaigns\subitem2\@maxvar3")=%% %%=contentareabyname("my contents\campaigns\subitem3\@maxvar4")=%% %%=contentareabyname("my contents\campaigns\subitem4\@maxvar5")=%% 

if, example values of fields are:

field1: 10 field2: 15 field3: 5 field4: 1 field5: 100 

then 5 contentareas should populated following:

%%=contentareabyname("my contents\campaigns\mainitem\@field5")=%% %%=contentareabyname("my contents\campaigns\subitem1\@field2")=%% %%=contentareabyname("my contents\campaigns\subitem2\@field1")=%% %%=contentareabyname("my contents\campaigns\subitem3\@field3")=%% %%=contentareabyname("my contents\campaigns\subitem4\@field4")=%% 

passing values amscript values easy. desc sorting them , passing them on appropiate @maxvar seems bit of stretch limited ampscript knowledge.

any appreciated!

excellent question. i'd unpivot data extension query activity , use lookuporderedrows() function retrieve them in proper order. sorting of pain in ampscript.

the t-sql unpivot() function won't work on et/sfmc, you'll need old-school way in query activity:

select  emailaddress , field , fieldvalue [unpivot-test] cross apply (   select 'field1', field1 union   select 'field2', field2 union   select 'field3', field3 union   select 'field4', field4 union   select 'field5', field5 ) c (field, fieldvalue) 

i have several ampscript lookup examples on my blog. here's lookuporderedrows() one:

%%[ var @rows, @row, @rowcount, @numrowstoreturn, @lookupvalue, @i  set @lookupvalue = "whee" set @numrowstoreturn = 0 /* 0 means */ set @rows = lookuporderedrows("dataextensionname",@numrowstoreturn,"decolumn1 desc, decolumn2 asc","lookupcolumn", @lookupvalue) set @rowcount = rowcount(@rows)  if @rowcount > 0  @i = 1 @rowcount  var @decolumn1, @decolumn2 set @row = row(@rows,@i) /*get row based on loop counter */ set @decolumn1 = field(@row,"decolumn1") set @decolumn2 = field(@row,"decolumn2")  ]%%  row %%=v(@i)=%%, decolumn1 %%=v(@decolumn1)=%%, decolumn2 %%=v(@decolumn2)=%%  %%[  next @i ]%%  %%[ else ]%%  no rows found  %%[ endif ]%% 

also, post future et/sfmc questions on over salesforce.stackexchange.com , tag them marketing-cloud. there's lot more people asking/answering questions on there.