Question: For SQL Server:

* Table Name = `Phones`
* Columns = id, mobileno, status, userid, createdon

Each user can have multiple numbers, I want to find oldest active phone number for each users. `Status=1` is active.

For example:

```
user1= id=1, mobileno=123, status=1,userid=1,createdon=2019/12/20
user1= id=2, mobileno=1234, status=1,userid=1,createdon=2019/12/19
user1= id=3, mobileno=12348, status=0,userid=1,createdon=2019/12/17

user2= id=4, mobileno=12345, status=1,userid=2,createdon=2019/12/15
user2= id=5, mobileno=123456, status=1,userid=2,createdon=2019/12/10

```

Result must be 

```
user1= id=1, mobileno=1234
user2= id=2, mobileno=123456

```

(id unique and mobileno = oldest active one)

Thanks.

Answer: 
You can use [row\_number()](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15) :

```
select t.id, t.mobileno
from (select t.*, row_number() over (partition by t.userid order by createdon) as seq
      from table t
      where t.status = 1
     ) t
where seq = 1;

```

If the `ID` is unique or auto incremented then you can use it in `order by` clause instead of `createdon`.
A simple and efficient method is a correlated subquery:

```
select t.*
from t
where t.createdon = (select min(t2.createdon)
                     from t t2
                     where t2.userid = t.userid
                    );

```