How to get your id from sql?

Question

SQL

Good day!
I ask you to help with a small task, you need to form sql like this:
There are 20 photos that belong to different users, how to make the ID of the 1st photo for each user start from 1 and then ascending?

Answer 1

well, the id sign | user_id | local_id | ...
1 | 1 | 1 | ...
2 | 2 | 1 | ...
3 | 1 | 2 | ...
4 | 3 | 1 | ...
5 | 2 | 2 | ...
6 | 1 | 3 | ...
id - continuous numbering
user_id - user id
local_id - photo id for this user
Then the request will be as follows:
Get the first photo of the second user
A while adding the next one, you need to get the highest value first

insert into photos (user_id, local_id, ...)
values (2, (select max(local_id)+1 from photos where user_id = 2 ), ...)

Answer 2

Only implement such logic in the application. Moreover, when deleting a photo, you will have to renumber the remaining ones.
But in general, this is a strange task, usually it makes no sense to do this.

Answer 3

When adding a photo, read the ID of the last one added for this user.
And assign a new ID = +1