TSQL -> Numerate logins records exercise

Suppose that we have user logins record (id + login time) and want to numerate logins per user in Ascending mode. Following script shows solution based on the use of ROW_NUMBER() function available in MS SQLServer-2005. Sample data for this exercise can be created by this script.

USE [colombo]
GO

/*
  create additional field that will store count of login records
  per userid
*/


ALTER TABLE UserLogins ADD cn INT;

UPDATE a
SET a.cn = b.cn
FROM UserLogins AS a INNER JOIN
   (SELECT userid, count(*) AS cn
   FROM UserLogins
   GROUP BY userid) AS b
ON a.userid = b.userid;

/*
Now we'll use system finction ROW_NUMBER() to create column of sequential numbers. This column will serve us as artificially created row-id. Following select creates numbers sequens based on quering system catalog view sys.objects that allways exists in the databse
*/

SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS n
FROM sys.objects;

/*
next query adds record numerator UserLogins table
*/

SELECT userid, createdate,
ROW_NUMBER() OVER (ORDER BY userid) AS idno
FROM UserLogins
ORDER BY userid, createdate;

Selected data will look like this :
useridcreatedateidno
4002007-11-05 12:38:00.0001
4002007-11-06 00:38:00.0002
4002007-11-06 06:38:00.0003
4012007-11-05 12:38:00.0004
4012007-11-06 00:38:00.0005
......

/* final select */

SELECT t1.userid, t2.createdate, t1.n
FROM
(
   SELECT a.userid, b.n, ROW_NUMBER() OVER (ORDER BY a.userid) AS idno
   FROM UserLogins AS a,
   (SELECT ROW_NUMBER() OVER (ORDER BY object_id) AS n
   FROM sys.objects)
  AS b
  WHERE b.n BETWEEN 1 AND a.cn
  GROUP BY a.userid, b.n
) AS t1
INNER JOIN
(
   SELECT userid, createdate,
   ROW_NUMBER() OVER (ORDER BY userid) AS idno
   FROM UserLogins
) AS t2
ON t1.idno = t2.idno
ORDER BY t1.userid, t2.createdate, t1.n;


Selected data will look like this :
useridcreatedateidno
4002007-11-05 12:38:00.0001
4002007-11-06 00:38:00.0002
4002007-11-06 06:38:00.0003
4002007-11-06 18:38:00.0004
4002007-11-07 00:38:00.0005
4012007-11-05 12:38:00.0001
4012007-11-06 00:38:00.0002
4012007-11-06 12:38:00.0003
4012007-11-07 00:38:00.0004
4022007-11-05 12:38:00.0001
4022007-11-06 00:38:00.0002
......



sqlexamples.info