Is it possible to generate consecutive ones based on the change of a column in SQL Server?

Question

Is it possible to generate consecutive ones based on the change of a column in SQL Server?

Navigation

#1 by (1 votes)

1

I have this case: I'm interested in SQL Server 2008, to be able to assign a row to the records but to reset it to 1 every time a date changes in the query.

For example I have these data:

And I am interested in generating a consecutive one that behaves in the following way:

It should be noted that the dates will always be ordered ascending. I do not know if you can use ROW_NUMBER () for this. I hope you can help me.

Thank you in advance!

sql sql-server query

asked by Jesús Rojas 01.12.2017 в 23:25

source

1 answer

Problem when switching from a second activity to a third AndroidStudio how to make this line read the delete key back

score 1 · Answer 1

I managed to solve the problem with the following query:

SELECT Z.DATO_ID
    ,Z.DATO1
    ,CAST(Z.FECHA AS DATE) AS FECHA
    ,ROW_NUMBER() OVER (
        PARTITION BY CAST(Z.FECHA AS DATE) ORDER BY CAST(Z.FECHA AS DATE)
        ) AS CONSECUTIVO
FROM TABLA_ORIGEN Z WITH (NOLOCK)
ORDER BY Z.FECHA ASC