David Feldman David Feldman - 25 days ago 12
MySQL Question

MySQL: how to select the Nth value of each group with GROUP BY

I want to select the 2nd response column value of each new_threads group, with a zero as the value if it is a group of 1 row.

new_treads|response
------------------
1 | 0
1 | 1
2 | 0
2 | 0
2 | 1
... | ...
9 | 0
9 | 1
9 | 0
10 | 0


The output being:

new_treads|response
------------------
1 | 1
2 | 0
... | ...
9 | 1
10 | 0


So far, I understand how to get the first with MIN, but I need the 2nd

SELECT
thread,
min(response)
FROM messages
GROUP BY thread;


I would like to use GROUP BY because I'm using GROUP BY for other SELECTs as well

Thanks!

Answer

Since the rows are not "numbered", you need to create a number for each group and then select it. I'd do that with user variables:

select thread, response
from (
        select @n := (case 
                when m.thread = @prev_thread then @n 
                else 0 
            end) + 1 as n    -- If the current thread is the same as the
                             -- previous row, then increase the counter,
                             -- else, reset it
             , @prev_thread := m.thread as thread -- Update the value of
                                                  -- @prev_thread
             , m.response
        from 
               (select @n := 0, @prev_thread := 0) as init
                          -- The 'init' subquery initializes the 
                          -- temp variables:
                          --     @n is a counter
                          --     @prev_thread is an identifier for the
                          --     previous thread id
             , messages as m
        order by m.thread -- You need to add a second column to order 
                          -- each response (such as "response_id", or
                          -- something like that), otherwise the returned
                          -- row may be a random one
    ) as a
where n = 2;    -- Select the rows from the subquery where the counter equals 2

The above works quite fine to find the 2nd row of each group, but only if there's one. So now: how to get a NULL value if there isn't a second row?

The easiest way to do this would be to use a left join:

select t.thread, b.response
from (select distinct thread from messages) as t
     left join (
         -- Put the above query here
     ) as b on t.thread = b.thread;
Comments