How to display the difference in user data in the first visit and in the second?

Question

SQL

Sorry for the poorly formed question. But now I will explain the point.
There is a table with records where about 200 records. All of them are formed in 5 columns.
They include (id, gender, date, cause, effect). Here is an example of such entries:

There are 2 entries in the table for each id. Those. for example:

As you can see, each id in the table occurs 2 times. One earlier, the other later by date (one user did not come 2 times in one day). How to correctly calculate for example this:
Displays all people who have Polyps for the first time. and normal and the second time CRC and Futher Procedure ?

Answer 1

You can try like this:

select t1.id
  from table t1
 where t1.my_column_4 = 'Polyps'
   and t1.my_column_5 = 'normal'
   and exists(select 1
                from table t2
               where t2.id          = t1.id
                 and t2.Date        > t1.Date
                 and t2.my_column_4 = 'CRC'
                 and t2.my_column_5 = 'Futher Procedure')

If you have MSSQL, then you can use FIRST_VALUE, more details here:
https://docs.microsoft.com/en-us/sql/t-sql/functio...