group by - Is it possible to add a rank to a continuous data blocks based on ordered datetime stamps in SQL Server 2008? -

group by - Is it possible to add a rank to a continuous data blocks based on ordered datetime stamps in SQL Server 2008? -

my log server collects info client rfid readers. tagscantimestamp row creation datetime in utc

following live sample data:

incomingip readerid tagid tagscantimestamp 183.82.1.192 02 000007 2014-11-07 02:55:24.000 183.82.1.192 02 000007 2014-11-07 02:55:25.000 183.82.1.192 02 000007 2014-11-07 02:55:26.000 49.206.4.53 01 000003 2014-11-07 02:58:17.000 49.206.4.53 01 000003 2014-11-07 02:58:41.000 49.206.4.53 01 000003 2014-11-07 03:00:00.000 49.206.4.53 01 000003 2014-11-07 03:01:37.000 49.206.4.53 04 000003 2014-11-07 03:02:23.000 49.206.4.53 04 000003 2014-11-07 03:05:18.000 49.206.4.53 04 000003 2014-11-07 03:06:11.000 49.206.4.53 01 000002 2014-11-07 03:07:17.000 49.206.4.53 01 000002 2014-11-07 03:08:53.000 49.206.4.53 01 000002 2014-11-07 03:10:46.000 49.206.4.53 01 000002 2014-11-07 03:12:12.000 49.206.4.53 01 000003 2014-11-07 03:14:14.000 49.206.4.53 01 000003 2014-11-07 03:16:21.000 49.206.4.53 01 000003 2014-11-07 03:16:45.000 49.206.4.53 01 000003 2014-11-07 03:17:28.000 183.82.1.192 02 000004 2014-11-07 03:20:43.000 183.82.1.192 02 000004 2014-11-07 03:20:44.000

.... when run next query

select incomingip, readerid, tagid, tagscantimestamp, rank() on (partition incomingip, readerid, tagid order tagscantimestamp) identityorder rt_tag_log_raw order 4

i getting next results:

incomingip readerid tagid tagscantimestamp identityorder 183.82.1.192 02 000007 2014-11-07 02:55:24.000 1 183.82.1.192 02 000007 2014-11-07 02:55:25.000 2 183.82.1.192 02 000007 2014-11-07 02:55:26.000 3 49.206.4.53 01 000003 2014-11-07 02:58:17.000 1 49.206.4.53 01 000003 2014-11-07 02:58:41.000 2 49.206.4.53 01 000003 2014-11-07 03:00:00.000 3 49.206.4.53 01 000003 2014-11-07 03:01:37.000 4 49.206.4.53 04 000003 2014-11-07 03:02:23.000 1 49.206.4.53 04 000003 2014-11-07 03:05:18.000 2 49.206.4.53 04 000003 2014-11-07 03:06:11.000 3 49.206.4.53 01 000002 2014-11-07 03:07:17.000 1 49.206.4.53 01 000002 2014-11-07 03:08:53.000 2 49.206.4.53 01 000002 2014-11-07 03:10:46.000 3 49.206.4.53 01 000002 2014-11-07 03:12:12.000 4 49.206.4.53 01 000003 2014-11-07 03:14:14.000 4 49.206.4.53 01 000003 2014-11-07 03:16:21.000 5 49.206.4.53 01 000003 2014-11-07 03:16:45.000 6 49.206.4.53 01 000003 2014-11-07 03:17:28.000 7 183.82.1.192 02 000004 2014-11-07 03:20:43.000 1 183.82.1.192 02 000004 2014-11-07 03:20:44.000 2

and want identityorder display follows

incomingip readerid tagid tagscantimestamp identityorder 183.82.1.192 02 000007 2014-11-07 02:55:24.000 1 183.82.1.192 02 000007 2014-11-07 02:55:25.000 1 183.82.1.192 02 000007 2014-11-07 02:55:26.000 1 49.206.4.53 01 000003 2014-11-07 02:58:17.000 2 49.206.4.53 01 000003 2014-11-07 02:58:41.000 2 49.206.4.53 01 000003 2014-11-07 03:00:00.000 2 49.206.4.53 01 000003 2014-11-07 03:01:37.000 2 49.206.4.53 04 000003 2014-11-07 03:02:23.000 3 49.206.4.53 04 000003 2014-11-07 03:05:18.000 3 49.206.4.53 04 000003 2014-11-07 03:06:11.000 3 49.206.4.53 01 000002 2014-11-07 03:07:17.000 4 49.206.4.53 01 000002 2014-11-07 03:08:53.000 4 49.206.4.53 01 000002 2014-11-07 03:10:46.000 4 49.206.4.53 01 000002 2014-11-07 03:12:12.000 4 49.206.4.53 01 000003 2014-11-07 03:14:14.000 5 49.206.4.53 01 000003 2014-11-07 03:16:21.000 5 49.206.4.53 01 000003 2014-11-07 03:16:45.000 5 49.206.4.53 01 000003 2014-11-07 03:17:28.000 5 183.82.1.192 02 000004 2014-11-07 03:20:43.000 6 183.82.1.192 02 000004 2014-11-07 03:20:44.000 6

i want identityorder incremented 1 whenever there alter in incomingip or readerid or tagid.

i have tried dense_rank() , couldn't accomplish want. please allow me know, if still not clear in explaning requirement.

any help highly appreciated.

thank you

isim right, haven't plenty input reply question. think talking rank() on (...) http://msdn.microsoft.com/en-us/library/ms176102.aspx. allow grouping , rank records according custom comparisons:

create table #scans ( id int, name varchar(50), timestamp int ) go insert #scans values (1, 'soda', 1111111), (2, 'coke', 1111111), (3, 'beer', 1111113), (4, 'wine', 1111113), (5, 'water', 1111114), (6, 'water', 1111115), (7, 'soda', 1111111) go select id, name, timestamp ,rank() on (partition timestamp order name desc) rank #scans order rank, name; go select id, name, timestamp ,rank() on (partition name order timestamp desc) rank #scans order rank, name; go drop table #scans; go

sql-server group-by ranking