1

I have a my SQL results similar to below. I have been using rails and would like to have solution either in SQL or Rails 

    id | name | addr | add_id
    -------------------------
    1 |   m  |   Q   |  15
    2 |   n  |   Q   |   3
    3 |   o  |   Q   |  37   
    4 |   f  |   R   |   8
    5 |   c  |   A   |   1
    6 |   r  |   M   |   6
    7 |   v  |   W   |  20
    8 |   z  |   T   |   4

What I was trying is to get only one row if there are multiple rows having common data in one of the column. For example, in the above results, I have Id 1, 2 and 3 having common addr as Q. However, I would like to eliminate this behavior and want my results as below only taking one of the row's if there is repetition on one column. Anticipated results.

    id | name | addr | add_id
    -------------------------
    1 |   m  |   Q   |  15   
    4 |   f  |   R   |   8
    5 |   c  |   A   |   1
    6 |   r  |   M   |   6
    7 |   v  |   W   |  20
    8 |   z  |   T   |   4
GMB
  • 216,147
  • 25
  • 84
  • 135
ZZzzZZzz
  • 1,800
  • 3
  • 29
  • 51

1 Answers1

1

In Postgres, distinct on comes handy for this:

select distinct on(addr) t.*
from mytable t
order by addr, id

If you do want to maintain the ordering by id in the resultset, you can wrap the query:

select *
from (select distinct on(addr) t.* from mytable t order by addr, id) t
order by id

Demo on DB Fiddle:

id | name | addr | add_id
-: | :--- | :--- | -----:
 1 | m    | Q    |     15
 4 | f    | R    |      8
 5 | c    | A    |      1
 6 | r    | M    |      6
 7 | v    | W    |     20
 8 | z    | T    |      4
GMB
  • 216,147
  • 25
  • 84
  • 135
  • The above results are from a query. So, Ideally that query will be a subquery for the query you have posted. – ZZzzZZzz Jan 18 '20 at 23:06
  • 1
    @ZZzzZZzz: possibly, you can just add `distinct on` directly on your existing query. If that's not possible for some reason, then you can indeed use a subquery (ie replace `mytable ` in my query with your query). – GMB Jan 18 '20 at 23:09
  • When tried to put my query into your sample, I got the expected results except for the fact that the remaining duplicate records are shown at the bottom of the results instead of being removed. – ZZzzZZzz Jan 18 '20 at 23:42