How to group by spelling column

Question

While working with some outdated data, I want to group data by column, ignoring spelling errors. I think SOUNDEX () can do the job to achieve the desired result. Here is what I tried:

SELECT soundex(AREA) FROM MASTER GROUP BY soundex(AREA) ORDER BY soundex(AREA) 

But (obviously) SOUNDEX returned the 4-character code in the result lines, like this, having lost the actual lines:

 A131 A200 A236 

How could I include at least one occurrence from a group in the query result instead of a 4-character code.

Answer 1

 SELECT soundex(AREA) as snd_AREA, min(AREA) as AREA_EXAMPLE_1, max(AREA) as AREA_EXAMPLE_2 from MASTER group by soundex(AREA) order by AREA_EXAMPLE_1 ; 

In MySQL, you can select group_concat (different AREA) as list_area to get all versions, and I don’t know about it in SQL Server, but min and max give two examples of these areas, and you wanted to refuse the difference anyway.

Answer 2

You can also use row_number() to get one row for each soundex(area) value:

 select AREA, snd from ( select AREA, soundex(AREA) snd, row_number() over(partition by soundex(AREA) order by soundex(AREA)) rn from master ) x where rn = 1 

See SQL Fiddle with Demo