From the input data, I need to rank the id. The data is order by time ascending.
Example: id number 421492036 has two record with code '05' and is ordered by time ascending.
The ID 421492036 has same code 05 and is the first two records in the table.
So its rank should be 1 in Output 1 table.
In Output 2 table we need to take the id 421492036 first record based on time.
Is it possible to get like this?
According to the SQL Server Documentation, DENSE_RANK() is available on all supported versions of SQL Server. If you upgrade to one of the current versions you can run the query:
select max(id), max(code), max(time), rk
from (
select t.*, dense_rank() over(order by time) as rk from t
) x
group by rk
You can do the TOP 1 WITH TIES to get the first row only:
create table #t_id(id int, code varchar(30), time datetime)
insert into #t_id (id, code, time)
select 1, '05', '20200101'
union all
select 1, '05', '20200102'
union all
select 3, '05', '20200101'
select TOP 1 WITH TIES *
from #t_id t
ORDER BY (SELECT COUNT(*) FROM #t_id t2 WHERE t2.id = t.id AND t2.time < t.time)
COUNT(*) FROM #t_id t2 WHERE t2.id = t.id AND t2.time < t.time can be used to do the RANKing the '2005 way
