1

Specifically, I have multiple clicks associated with a session, and there is a datetime corresponding to each click. For each session, I would like to select as fields 'first_click_datetime', 'second_click_datetime', 'third_click_datetime', and so on.

So far, I have something like this:

SELECT session_id, 
min(click_datetime) as first_click_datetime,
CASE
  when total_number_of_clicks = 2 then max(click_datetime)
/* when total_number_of_clicks >2 then ???? */
  else null
END as second_click_datetime
FROM table1 GROUP BY 1;

How can I get the 2nd, 3rd, 4th, etc. click_datetime?

Thanks!

Marina
  • 3,222
  • 5
  • 25
  • 35
  • You might be looking for rank over functionality.. http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql – Chris Gessler May 18 '12 at 12:15

1 Answers1

1
SELECT  session_id,
        (
        SELECT  click_datetime
        FROM    table1 t1
        WHERE   t1.session_id = td.session_id
        ORDER BY
                session_id DESC, click_datetime DESC, id DESC
        LIMIT   1
        ) AS first_click,
        (
        SELECT  click_datetime
        FROM    table1 t1
        WHERE   t1.session_id = td.session_id
        ORDER BY
                session_id DESC, click_datetime DESC, id DESC
        LIMIT   1, 1
        ) AS second_click,

        (
        SELECT  click_datetime
        FROM    table1 t1
        WHERE   t1.session_id = td.session_id
        ORDER BY
                session_id DESC, click_datetime DESC, id DESC
        LIMIT   2, 1
        ) AS third_click
FROM    (
        SELECT  DISTINCT session_id
        FROM    table1
        ) td

Create an index on (session_id, click_datetime, id) for this to work fast.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • What is the field 'id' here? This looks like it could work, but it would actually make my code really messy; when I say 'table1' in my sql code, it's really selects joined with other selects. Thank you, though! – Marina May 18 '12 at 14:37
  • 1
    @Marina, you can create a view `table1` as refer to it as a table. – vyegorov May 18 '12 at 15:48