Sal Sal - 1 year ago 59
MySQL Question

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.

Answer Source

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.