Question about row_number
-
Hi to all, I have a question about row_number
I have this table
PK ID STATO DATEANDTIME
1 2 CREATO 2015-01-01 08:00:00
2 2 LAVORATO 2015-01-01 18:00:00
3 2 LAVORATO 2015-01-01 19:00:00
4 2 IN ATTESA 2015-02-01 08:00:00
5 2 IN ATTESA 2015-02-01 10:00:00
6 2 LAVORATO 2015-02-01 18:00:00
7 2 FINITO 2015-02-02 08:00:00
8 2 FINITO 2015-02-02 09:00:00
Using the following query
select ID,STATO,DATEANDTIME,
row_number() over (partition ID, STATO ORDER BY PK) as myrownumber
from MYTABLE
This is the output
2 CREATO 2015-01-01 08:00:00 1
2 LAVORATO 2015-01-01 18:00:00 1
2 LAVORATO 2015-01-01 19:00:00 2
2 IN ATTESA 2015-02-01 08:00:00 1
2 IN ATTESA 2015-02-01 10:00:00 2
2 LAVORATO 2015-02-01 18:00:00 3
2 FINITO 2015-02-02 08:00:00 1
2 FINITO 2015-02-02 09:00:00 2
I'd like the row
2 LAVORATO 2015-02-01 18:00:00 3 comes out with myrownumber = 1
To summarize I need to reset myrownumber counter each time, for the same ID, the column STATO changes from previous value (rows are ordered by DATEANDTIME ASC)
Is it possible?
Can someone help me?
Thanks in advance for the help.
Best regards
Fab
-
This should work for you
with cte as (
select ID,STATO,DATEANDTIME,
row_number() over (partition by ID ORDER BY DATEANDTIME) -
row_number() over (partition by ID, STATO ORDER BY DATEANDTIME) as grp
from MYTABLE)
select ID,STATO,DATEANDTIME,
row_number() over (partition by ID, STATO, grp ORDER BY DATEANDTIME) as myrownumber
from CTE
order by DATEANDTIME;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
It works as expected, thanks a lot!!!!!!!
Fab
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply