Search code examples
postgresqlrails-postgresqlgroupwise-maximum

How to get unique value after removing space and group by some different column in postgres

I have a table like 

id type note
1   A   ab cd
1   B   cdef
1   A   abd
1   A   abcd

I want to get all the unique notes after removing space so my result will be like this

id type note
1   A   ab cd
1   A   abd
1   B   cdef

because after removing space ab cd and abcd will be same. I am using Postgres. Please note that id column in not unique.

Solution

  • It's a classic groupwise maximum but you just group it on an expression not column

    SELECT id, type, note FROM
  (SELECT DISTINCT ON(replace(note,' ',''))
     id
     ,type
     ,note
   FROM table1
   ORDER BY replace(note,' ','')
  ) AS q

    sqlfiddle