I have this SQL Query:
SELECT
sg.rolesID,
count# = ROW_NUMBER() OVER (ORDER BY sg.rolesContainerID),
sg.rolesContainerID,
eventTimeStamp AS timeStarted,
eventTimeStamp AS timeFinished
FROM
view_SecurityGroup sg
WHERE
sg.rolesID = '22400ab8-62a2-4a69-b002-5dd4d0459728'
ORDER BY
sg.rolesContainerID, timeStarted, timeFinished DESC
With these results:
rolesID count# rolesContainerID timeStarted timeFinished
----------------------------------------------------------------------------------------------------------------------------------------------------
22400AB8-62A2-4A69-B002-5DD4D0459728 1 153F36C8-6FB5-4B50-A2D7-000209C96663 2023-02-27 14:26:50.583 2023-02-27 14:26:50.583
22400AB8-62A2-4A69-B002-5DD4D0459728 2 153F36C8-6FB5-4B50-A2D7-000209C96663 2023-02-27 14:26:50.587 2023-02-27 14:26:50.587
22400AB8-62A2-4A69-B002-5DD4D0459728 3 153F36C8-6FB5-4B50-A2D7-000209C96663 2023-02-27 14:26:58.420 2023-02-27 14:26:58.420
22400AB8-62A2-4A69-B002-5DD4D0459728 4 790E699D-1BDE-4CCA-B6A6-013696B81774 2023-02-28 15:41:02.540 2023-02-28 15:41:02.540
22400AB8-62A2-4A69-B002-5DD4D0459728 5 790E699D-1BDE-4CCA-B6A6-013696B81774 2023-02-28 15:42:51.540 2023-02-28 15:42:51.540
22400AB8-62A2-4A69-B002-5DD4D0459728 6 790E699D-1BDE-4CCA-B6A6-013696B81774 2023-02-28 15:44:27.560 2023-02-28 15:44:27.560
22400AB8-62A2-4A69-B002-5DD4D0459728 7 223C4704-2B04-404D-991A-04B408C6CA93 2022-11-04 13:59:03.343 2022-11-04 13:59:03.343
22400AB8-62A2-4A69-B002-5DD4D0459728 8 223C4704-2B04-404D-991A-04B408C6CA93 2022-11-04 13:59:03.347 2022-11-04 13:59:03.347
22400AB8-62A2-4A69-B002-5DD4D0459728 9 223C4704-2B04-404D-991A-04B408C6CA93 2022-11-04 14:05:49.133 2022-11-04 14:05:49.133
22400AB8-62A2-4A69-B002-5DD4D0459728 10 229105A8-2713-44FE-B0D0-0FF09F54E905 2023-02-27 00:28:58.840 2023-02-27 00:28:58.840
22400AB8-62A2-4A69-B002-5DD4D0459728 11 229105A8-2713-44FE-B0D0-0FF09F54E905 2023-02-27 00:29:48.513 2023-02-27 00:29:48.513
But I want the count# to only be incremented for each rolesContainerID...like this:
rolesID count# rolesContainerID timeStarted timeFinished
----------------------------------------------------------------------------------------------------------------------------------------------------
22400AB8-62A2-4A69-B002-5DD4D0459728 1 153F36C8-6FB5-4B50-A2D7-000209C96663 2023-02-27 14:26:50.583 2023-02-27 14:26:50.583
22400AB8-62A2-4A69-B002-5DD4D0459728 1 153F36C8-6FB5-4B50-A2D7-000209C96663 2023-02-27 14:26:50.587 2023-02-27 14:26:50.587
22400AB8-62A2-4A69-B002-5DD4D0459728 1 153F36C8-6FB5-4B50-A2D7-000209C96663 2023-02-27 14:26:58.420 2023-02-27 14:26:58.420
22400AB8-62A2-4A69-B002-5DD4D0459728 2 790E699D-1BDE-4CCA-B6A6-013696B81774 2023-02-28 15:41:02.540 2023-02-28 15:41:02.540
22400AB8-62A2-4A69-B002-5DD4D0459728 2 790E699D-1BDE-4CCA-B6A6-013696B81774 2023-02-28 15:42:51.540 2023-02-28 15:42:51.540
22400AB8-62A2-4A69-B002-5DD4D0459728 2 790E699D-1BDE-4CCA-B6A6-013696B81774 2023-02-28 15:44:27.560 2023-02-28 15:44:27.560
22400AB8-62A2-4A69-B002-5DD4D0459728 3 223C4704-2B04-404D-991A-04B408C6CA93 2022-11-04 13:59:03.343 2022-11-04 13:59:03.343
22400AB8-62A2-4A69-B002-5DD4D0459728 3 223C4704-2B04-404D-991A-04B408C6CA93 2022-11-04 13:59:03.347 2022-11-04 13:59:03.347
22400AB8-62A2-4A69-B002-5DD4D0459728 3 223C4704-2B04-404D-991A-04B408C6CA93 2022-11-04 14:05:49.133 2022-11-04 14:05:49.133
22400AB8-62A2-4A69-B002-5DD4D0459728 4 229105A8-2713-44FE-B0D0-0FF09F54E905 2023-02-27 00:28:58.840 2023-02-27 00:28:58.840
22400AB8-62A2-4A69-B002-5DD4D0459728 4 229105A8-2713-44FE-B0D0-0FF09F54E905 2023-02-27 00:29:48.513 2023-02-27 00:29:48.513
Is there a way to do that in SQL Server?
Thanks!
count# = DENSE_RANK() OVER (ORDER BY sg.rolesContainerID),
or
DENSE_RANK() OVER (ORDER BY sg.rolesContainerID) AS count#,