calculate column value in sql

Question

I have a table in a MySQL db that looks like that:

SELECT * FROM pi;

Employee_id Year 1000 2014 1000 2015 1001 2014 1002 2014 1002 2015 1003 2015 1004 2015

When I run the following query:

SELECT Employee_id, MIN(Year) FROM pi GROUP BY Employee_id;

I get:

Employee_id Year 1000 2014 1001 2014 1002 2014 1003 2015 1004 2015

Now I want to add a new column to the original table values that should have a '0' if the value of the Year for the row equals the MIN(Year) for the Employee_id or a '1' if it doesn't. I've tried:

SELECT Employee_id, MIN(Year),
 (CASE WHEN(SELECT MIN(Year) FROM pi WHERE Employee_id = Employee_id < Year) THEN '0' ELSE '1' END) New
FROM pi
GROUP BY Employee_id;

And this produces:

Employee_id Year New 1000 2014 0 1001 2014 0 1002 2014 0 1003 2015 0 1004 2015 0

On the other hand, when I tried:

SELECT Employee_id, MIN(Year),
  (CASE WHEN(SELECT MIN(Year) FROM pi 
WHERE Employee_id = Employee-id
GROUP_BY employee_id < Year) THEN '0' ELSE '1' END) New
FROM pi;

I got just:

Employee_id Year New 1000 2014 1

I've tried a bunch of additional querys, but all of them rendered error messages or the same results than those two, so I'm kind of stuck here. Any suggestions? Thank you.

Answer 1

You could use a join ond the min table

select t.Employee_id, t.min_year
  , case when pi.year = t.min_year then 0 else 1 end as my_check
from pi inner join (

    SELECT Employee_id, MIN(Year) as min_year
    FROM pi
    GROUP BY Employee_id
)  t on t.Employee_id = pi.Employee_id