Sal Sal - 5 months ago 19
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

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.

Comments