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;