Que counting

Hi,
I have data as below

Date ID Lesson Group
01-01-18 M001 Math 001
02-01-18 M002 Aljabar 001
03-01-18 C001 Swimming 002
04-01-18 C002 Basket ball 002
05-01-18 B001 Drawing 003
05-02-18 B002 Singing 003

based on the group I want to make the data resulting

Date ID Lesson Group que
01-01-18 M001 Math 001 1
02-01-18 M002 Aljabar 001 1
03-01-18 C001 Swimming 002 2
04-01-18 C002 Basket ball 002 2
05-01-18 B001 Drawing 003 3
05-02-18 B002 Singing 003 3

que means that if the group is same as next group record it will put the same que as before.
please help..

thx,

Joe

I think the LAG function will help you here

Hi Joe,
Netiquette for posting example data is to post create table and insert statements. Make it easy for people to help you. If you're on 2008R2 (this topic) then LAG isn't available. You could use a CTE and row_number () over (parition by Lesson, Group, Que order by [Date ID]) n and in the final select add where n=1.