CODEWITHSUNDEEP
sqlsql-server
massi
massi

Reputation: 97

Create SQL Server View with progressive field only regarding some other fields

Let's assume I have a table like this one below:

enter image description here

Now, from that table I would like to create a view like this one below:

enter image description here

Number is a progressive counter per EventId, for example, first row with EventId EVENT1 has Number equals to 1, second row with EventId EVENT1 has Number equals to 2, and so on

Upvotes: 1

Views: 122

Answers (3)

DineshDB
DineshDB

Reputation: 6193

Try this,

SELECT *
    ,SUM(1) OVER(PARTITION BY eventid ORDER BY userid,timestamps) Number
FROM Your_Table
ORDER BY timestamps

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

You can use Row_number()

   declare @myt table
(eventid nvarchar(50),userid nvarchar(50),timestamps datetime)

insert into @myt
values
('Event1','User1','2017-10-30 00:00:01.000'),
('Event2','User2','2017-10-30 00:00:10.000'),
('Event1','User2','2017-10-30 00:00:11.000'),
('Event3','User2','2017-10-30 00:00:12.000'),
('Event2','User1','2017-10-30 00:00:10.000'),
('Event3','User1','2017-10-30 00:00:11.000')


select *,ROW_NUMBER() over(partition by eventid order by timestamps) from @myt
order by timestamps

You dont need the last order by, but thats so it looks like your example.

Upvotes: 1

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

ROW_NUMBER() OVER (PARTITION BY EventId ORDER BY TimeStamp)

Upvotes: 2

Related Questions