Get all the 1st data of the reference key in SQL

Question

I have two tables named "Users" and "Payments". In Payment, there is a foreign key to the users table. This is a record of the payment schedule.

Select id,User_id from payment;

id	User_id
1	1
2	1
3	2
4	3
5	2

But my expected output is this.

id	User_id
1	1
3	2
4	3

I only want the first data for each User_id in SQL.

Answer 1

You can use the GROUP BY clause on the User_id column.

Answer 2

Try this...

SELECT p1.id, p1.user_id
FROM payment p1
WHERE p1.id IN (SELECT MIN(p2.id) FROM payment p2 GROUP BY p2.user_id)

Suppose, "first" refers to the smallest value of id in the payment table for that user, and you also need more details in the payment table or perform other operations on it...

If you just want the MIN payment ID, then just do this:

SELECT MIN(id), user_id FROM payment GROUP BY user_id