CODEWITHSUNDEEP
mysqlselectgroup-bydenormalization
Marina
Marina

Reputation: 3322

In a MySQL GROUP BY, how can one select the nth value --not the max or min, but 2nd, for example?

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!

Upvotes: 1

Views: 1093

Answers (1)

Quassnoi
Quassnoi

Reputation: 425251

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.

Upvotes: 1

Related Questions