Keep the “first” value using groupby – MYSQL
Input :
col1 col2
a 1
b 1
c 2
d 2
I would like to :
Create the new colX to get the first value in col1 group by col2 and save the value in colX
results :
col1 col2 colX
a 1 a
b 1 a
c 2 c
d 2 c
how can do it in mysql
Software/Hardware used:
col1 col2
a 1
b 1
c 2
d 2
I would like to :
Create the new colX to get the first value in col1 group by col2 and save the value in colX
results :
col1 col2 colX
a 1 a
b 1 a
c 2 c
d 2 c
how can do it in mysql
Software/Hardware used:
ASKED:
October 14, 2010 7:51 PM
UPDATED:
October 15, 2010 6:04 PM
Answer Wiki:
In MySql, you will probably need to do something like this:
<pre>alter table yourTable add colX char;
update yourTable t
set colX = (select min from (select col2,min(col1) min
from yourTable
group by col2) temp1
where col2 = t.col2);</pre>
Note that this may work, but it won't be efficient.
The inner subquery is needed because MySql raises an error if you try to update a table and read from it directly in the same update command.
-----------------------
To see all answers submitted to the Answer Wiki: View Answer History.
wOW , THANK YOU !