SQL DISTINCT RETRIEVING ALL OTHER COLUMNS OF THE SAME RECORD

Question

I'm using PostgreSQL

I have a table with information about prospect clients, that looks like this:

ID | Phone        | link
1  | 3105637247   | https://link_for_id_1
2  | 3105637247   | https://link_for_id_2
3  | 3105637247   | https://link_for_id_3 
4  | 3153333651   | https://link_for_id_4
5  | 3153333651   | https://link_for_id_5

The aim is to use this data for reporting to the sales team. But the requirement is that there will not be repeated phone numbers. In this case, my desired output would be:

ID | Phone        | link
1  | 3105637247   | https://link_for_id_1
5  | 3153333651   | https://link_for_id_5

For that purpose i'm using this query:

SELECT DISTINCT Phone,
                max(ID), -- Here using aggregated functions to allow ID and link to visualize
                max(link) 
FROM MyTable

But this approach sometimes give me links that not correspond to my ID's:

ID | Phone        | link
1  | 3105637247   | https://link_for_id_3
5  | 3153333651   | https://link_for_id_4

Is there a way to retrieve unique Phones with the condition that the ID and Link correspond to the same record?

Hi @LeandroBardelli, as you can see the last character of the link is the same as the id. This is the way I know what is the correct result. — David López, Nov 11 '21 at 22:12
I know, but why do you "remove" 2 and 3? Easily you could say that the correct link is 3. — Leandro Bardelli, Nov 11 '21 at 22:13
For eg: Why `1 | 3105637247 | https://link_for_id_1` and not `3 | 3105637247 | https://link_for_id_3` — Leandro Bardelli, Nov 11 '21 at 22:15
@LeandroBardelli, this is just a subset of the table, each record is a different product that is published by the same number, for that reason has different links. It doesn't matter what to choose, if ID 1 or ID 3, but it matters that the link and ID correspond to the same record — David López, Nov 11 '21 at 22:15
About `DISTINCT ON`: https://stackoverflow.com/a/7630564/939860 — Erwin Brandstetter, Nov 11 '21 at 23:41

jarlh · Accepted Answer · 2021-11-11T22:19:56.363

1

You can use Postgresql's DISTINCT ON:

select distinct on (Phone) t.*
from Mytable t
order by Phone

Or do NOT EXISTS:

select t.*
from Mytable t
where not exists (select 1 from Mytable t2
                  where t2.Phone = t1.Phone
                    and t2.id < t1.id)

edited Nov 11 '21 at 22:19

answered Nov 11 '21 at 22:12

jarlh

42,561
8
45
63

SQL DISTINCT RETRIEVING ALL OTHER COLUMNS OF THE SAME RECORD

1 Answers1