SQL Query, loop through data

I have a database with the following fields: UserID, DateRecorded and Score.

A new entry is added to the table each time the score is updated. Here is an example of a user record:

UserID | DateRecorded | Score
3 | 21-08-2019 | 10
3 | 23-08-2019 | 5
3 | 21-09-2019 | 10
3 | 21-08-2018 | 1

From the query I would like it to show me only the scores of all users with the earliest DateRecorded.

Although not much familiar with cursors, I tried using an example from the net and adapted it to mine, but that doesn’t seem to have worked. Here is my attempt with the cursor:

DECLARE @DateRecorded AS DATETIME;
DECLARE @scores AS FLOAT;
DECLARE @Cursor as CURSOR;

SET @Cursor = CURSOR FOR
select [user_id], [DateRecorded], scores
from myTable
where [user_id] = '55'

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @UserID, @DateRecorded, @scores;

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @UserID AS 'User ID', MIN(@DateRecorded) AS 'Date', @scores AS 'Score';
 FETCH NEXT FROM @Cursor INTO @UserID, @DateRecorded, @scores;
END

CLOSE @Cursor;
DEALLOCATE @Cursor;

This is the results I’m expecting:

3 | 21-08-2018 | 1

Is there a simpler way of doing this than using cursors?

Have I completely missunderstood cursors?

Thanks for your help

Answer

Don’t use cursors and loops (unless you have no other choice): they are complicated, and very slow. What you are asking for here can be done with straight SQL querires.

If you just want the earliest record date per user and no other information, then simple aggregation is enough:

select UserIdD, min(DateRecorded) from mytable group by UserID

If you want the entire “first” record per user, then that’s a typical greatest-n-per-group problem. One method is to filter with a correlated subquery:

select t.*
from mytable t
where t.DateRecorded = (
    select min(t1.DateRecorded) from mytable t1 where t1.UserID = t.UserID
)

You might also like the anti-left join solution:

select t.*
from mytable t
left join mytable t1 on t1.UserID = t.UserID and t1.DateRecorded < t.DateRecorded
where t1.UserID is null