CODEWITHSUNDEEP
mysql
Sal
Sal

Reputation: 1693

mysql get minimum date, combine columns with group by

I have a MySQL table as follows: id, date and multiple int fields with 0/1. Only one int field in each row will contain a single 1 with the other columns all 0. Dates can be repeated for each id. 

id     | date         | isX | isY | isZ
-------+--------------+-----+-----+-----
111111 | '1994-04-09' | 0   | 1   | 0
222222 | '1991-07-29' | 1   | 0   | 0
222222 | '1991-07-29' | 0   | 1   | 0
333333 | '1993-06-29' | 0   | 0   | 1
333333 | '1993-06-29' | 0   | 1   | 0
333333 | '1996-04-09' | 0   | 1   | 0

I want to get a single row for each id, with the earliest date and a 1 in each int column if any row with that id and date have a 1 (0 otherwise).

id     | date         | isX | isY | isZ
-------+--------------+-----+-----+-----
111111 | '1994-04-09' | 0   | 1   | 0
222222 | '1991-07-29' | 1   | 1   | 0
333333 | '1993-06-29' | 0   | 1   | 1

I was using the following command to combine the int columns 

select id, 
    if(sum(isX)>0,1,0) as isX, 
    if(sum(isY)>0,1,0) as isY, 
    if(sum(isZ)>0,1,0) as isZ 
from table group by id;

but I wasn't sure how to use only those rows with the minimum date and how to add the date to the output.

Upvotes: 0

Views: 81

Answers (1)

JNevill
JNevill

Reputation: 50034

You were there, just add min(date)

select id, 
    min(`date`) as mindate,
    if(sum(isX)>0,1,0) as isX, 
    if(sum(isY)>0,1,0) as isY, 
    if(sum(isZ)>0,1,0) as isZ 
from table 
group by id;

If you didn't want rows for each id where the rows date is not the id's min(date) then you'll need a subquery:

select id, 
    `date`
    if(sum(isX)>0,1,0) as isX, 
    if(sum(isY)>0,1,0) as isY, 
    if(sum(isZ)>0,1,0) as isZ 
from table 
  INNER JOIN (SELECT id, min(date) as mindate FROM table GROUP BY id) sub1 ON
      table.id = sub1.id AND
      table.`date` = sub1.`date`
group by id, `date`;

Now the if(sum(isx... logic will only be used against records where the date is the min(date) for the id.

Upvotes: 1

Related Questions