SQL DISTINCT RETRIEVING ALL OTHER COLUMNS OF THE SAME RECORD-CodePudding

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?

CodePudding user response：

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)