CODEWITHSUNDEEP
mysqlgroup-by
David Feldman
David Feldman

Reputation: 359

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!

Upvotes: 0

Views: 2502

Answers (3)

Jed Lynch
Jed Lynch

Reputation: 2156

I would like to elaborate on the answer above. While it worked well for me, it took some time to piece together the context and generalize it so I could apply it to my code. I hope that this answer will better generalize what is laid out above...

SELECT *
FROM  (SELECT distinct keyField    --- keyField is the field the query is grouping by
        FROM TABLE
        --  Add keyField Constraint --
        --  Add non-keyField Constraint --     
INNER JOIN (SELECT *,
       @n:=(CASE                                -- Iterating through...
               WHEN keyField = @prev_keyField   -- When keyField value == previous keyField value
               THEN @n:=@n+1                    -- Define n as the row in the group
               ELSE 1                           -- When keyField value != previous keyField value, then n is the 1st row in the group
               END) as n,
            @prev_keyField:= keyField           -- Define previous keyField value for the next iteration
            FROM (SELECT @n:=0,@prev_keyField:=0) r,TABLE as p
            --  Add non-keyField Constraint--  
            ORDER BY keyField,sortField DESC    -- Order by keyField and the field you are sorting by
                                                --  ei. keyField could be `thread`, 
                                                --  and sort field could be `timestamp` if you are sorting by time
            ) s ON s.keyField = p.keyField
WHERE s.n = 2                                   -- Define which row in the group you want in the query

Upvotes: 0

Barranka
Barranka

Reputation: 21047

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;

Upvotes: 2

Joshua Byer
Joshua Byer

Reputation: 519

SELECT 
thread,
min(response)
FROM messages
GROUP BY thread
HAVING response > min(response)

try this just want to know if it works

Upvotes: 0

Related Questions