How to filter a table for duplicates from a specific column?

Question

PostgreSQL

Hello! I'm starting to study postgres and sql in general, but now I've already faced the task of filtering duplicates.
Help me figure it out :( There is a users
table with 4 columns: id , user_id, date, phone . user_id is unique. How can I not display a row if there is a duplicate user_id in it ? Here is a data query:

SELECT id,user_id,phone,date_time,id FROM  WHERE  phone IS NOT NULL ORDER BY id ASC;

I don’t understand where to stick DISTINCT user_id here ?

Answer 1

SELECT DISTINCT user_id, id,phone,date_time,id FROM WHERE phone IS NOT NULL ORDER BY id ASC;
Or else you can try
SELECT id,user_id,phone,date_time,id FROM WHERE phone IS NOT NULL GROUP BY user_id ORDER BY id ASC;

Answer 2

If you are only interested in the unique id itself, then:
If not unique, then you can use functions such as MIN, MAX, and some others. In general, some clarification on the task is required :)

Answer 3

I ask for a couple of clarifications. 1) user_id must be unique within the users table? (there can only be one row per user_id). Then you created the table incorrectly - this field must be a key one (well, or at least part of the key).
2) if not, then the task is reduced to what you want. If you want a list of unique userIds -

SELECT user_id from users
WHERE phone is not null
GROUP BY user_id

If you need something else - specify what you actually want to choose

Answer 4

select
t1.id,
t1.user_id,
t1.phone,
t1.date
from your_table t1
inner join 
(
  select 
  min(id) as min_id,
  user_id
  from your_table
  group by user_id
) t2 on t2.min_id = t1.id

Answer 5

SELECT DISTINCT ON (user_id) -- выводить уникальные записи по полю user_id 
user_id,id,phone,date_time -- что выводить
FROM users 
WHERE  phone IS NOT NULL 
ORDER BY id ASC; -- если найдены дубли, то выведется запись с наименьшим id