|
|
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 :
| userid | createdate | idno |
| 400 | 2007-11-05 12:38:00.000 | 1 |
| 400 | 2007-11-06 00:38:00.000 | 2 |
| 400 | 2007-11-06 06:38:00.000 | 3 |
| 401 | 2007-11-05 12:38:00.000 | 4 |
| 401 | 2007-11-06 00:38:00.000 | 5 |
| .. | .. | .. |
|
/* 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 :
| userid | createdate | idno |
| 400 | 2007-11-05 12:38:00.000 | 1 |
| 400 | 2007-11-06 00:38:00.000 | 2 |
| 400 | 2007-11-06 06:38:00.000 | 3 |
| 400 | 2007-11-06 18:38:00.000 | 4 |
| 400 | 2007-11-07 00:38:00.000 | 5 |
| 401 | 2007-11-05 12:38:00.000 | 1 |
| 401 | 2007-11-06 00:38:00.000 | 2 |
| 401 | 2007-11-06 12:38:00.000 | 3 |
| 401 | 2007-11-07 00:38:00.000 | 4 |
| 402 | 2007-11-05 12:38:00.000 | 1 |
| 402 | 2007-11-06 00:38:00.000 | 2 |
| .. | .. | .. |
|