How to get distinct values from Data Extension column

Question

Suppose I have data extension and it has multiple rows. I want to retrieve all values from one column and get only distinct values. Please find below screenshot

I want to create a picklist which I need distinct values from the City column.

Answer 1

First, you'll need to add a field in your Data Extension that has a static value (e.g. LookupKey, with a default value of 1). You can only do a lookup with an equals operator. Then you'll need to set it for your existing rows -- probably easiest with an export and overwrite import.

Once that's done, then you can use that LookupKey value in your lookup...something like this:

%%[

var @i, @cities, @row, @rowCount, @maxRows
var @City, @prevCity, @Area, @Salon, @Number

set @maxRows = 0 /* up to 2000 */

set @cities = LookupOrderedRows("Cities" ,@maxRows, "Number asc", "lookupKey","1")
set @rowCount = RowCount(@cities)
set @prevCity = ""

for @i = 1 to @rowCount do

  set @row = Row(@cities,@i)
  set @City = Field(@row, "City")
  set @Area = Field(@row, "Area")
  set @Salon = Field(@row, "Salon")
  set @Number = Field(@row, "Number")

  if @rowCount == 1 then

    output(concat("<ul>"))

  endif 

  if empty(@prevCity) or @City != @prevCity then

     set @prevCity = @City

     output(concat("<li>",@City,"</li>"))

  endif

  if @i == @rowCount then

    output(concat("</ul>"))

  endif 


next @i 

]%%

Reference

• De-duplicate rows in AMPScript