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

Question

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.

score 2 · Accepted Answer · answered Feb 10 '16 at 18:45

2

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

answered Feb 10 '16 at 18:45

Jakub Kania

15,665
2
37
47

its only returning unique value ... based on id , type .. not multiple records. – Sabyasachi Ghosh Feb 10 '16 at 19:23
It works exactly as in the example (see the fiddle). If it doesn't work as you wanted it to then please improve the question. – Jakub Kania Feb 10 '16 at 19:36
Hay it works on.. I had a different data set which confuse me .. It works :+1 – Sabyasachi Ghosh Feb 10 '16 at 20:41

score 0 · Answer 2 · answered Feb 10 '16 at 21:03

0

Since you appear to be using Rails (according to your tags), you could also use ActiveRecord to get all of your unique instances of note, disregarding any spaces:

WhateverModel.pluck("REPLACE(note, ' ', '')").uniq

answered Feb 10 '16 at 21:03

jeffdill2

3,968
2
30
47

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

2 Answers2