1

The MySQL database table:

create table t (
  visitor_id int(11),
  activity_type varchar(10),
  date date
);

The rows:

insert into t (visitor_id, activity_type, date) values (1, 'hit',   '2012-1-1');
insert into t (visitor_id, activity_type, date) values (1, 'event', '2012-1-2');
insert into t (visitor_id, activity_type, date) values (2, 'hit',   '2012-1-2');
insert into t (visitor_id, activity_type, date) values (2, 'event', '2012-3-5');
insert into t (visitor_id, activity_type, date) values (2, 'hit',   '2012-3-2');
insert into t (visitor_id, activity_type, date) values (1, 'hit',   '2012-3-5');
insert into t (visitor_id, activity_type, date) values (1, 'hit',   '2012-2-1');

I want to write a query to retrieve a data dump of users, activity type, and the order in which the activity occurred, that looks like the following: 

visitor_id, activity_type, Position 
1,          hit,              1 
1,          event,            2 
1,          hit,              3 
1,          hit,              4 
2,          hit,              1 
2,          hit,              2 
2,          event,            3

So far I have written the following solution:

select visitor_id, activity_type, 'Position'
from t1
order by visitor_id, date
;

The hard part is the column Position. This should represent the position in the order of the rows for that visitor ID. Is there any way to determine Position?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
davidjhp
  • 7,816
  • 9
  • 36
  • 56
  • SQL doesn't guarantee an order unless you specify an `ORDER BY` clause. `position` doesn't mean very much. If you TIMESTAMP everything (as opposed to date) that might give you a start –  Nov 01 '13 at 02:51
  • possible duplicate of [MySQL get row position in ORDER BY](http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by) – Walls Nov 01 '13 at 20:22

1 Answers1

1

This may be relevant to your question:

MySQL get row position in ORDER BY

Community
  • 1
  • 1
Code Krieger
  • 201
  • 1
  • 8