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

i want select 2nd response column value of each new_threads group, 0 value if 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, understand how first min, need 2nd

select  thread, min(response) messages group thread; 

i use group because i'm using group other selects well

thanks!

since rows not "numbered", need create number each group , select it. i'd user variables:

select thread, response (         select @n := (case                  when m.thread = @prev_thread @n                  else 0              end) + 1 n    -- if current thread same                              -- previous row, increase counter,                              -- else, reset              , @prev_thread := m.thread thread -- update value of                                                   -- @prev_thread              , m.response                         (select @n := 0, @prev_thread := 0) init                           -- 'init' subquery initializes                            -- temp variables:                           --     @n counter                           --     @prev_thread identifier                           --     previous thread id              , messages m         order m.thread -- need add second column order                            -- each response (such "response_id", or                           -- that), otherwise returned                           -- row may random 1     ) n = 2;    -- select rows subquery counter equals 2 

---

the above works quite fine find 2nd row of each group, if there's one. now: how null value if there isn't second row?

the easiest way use left join:

select t.thread, b.response (select distinct thread messages) t      left join (          -- put above query here      ) b on t.thread = b.thread;