Keep the “first” value using groupby – MYSQL

30 pts.
Tags:
GROUP BY statement
MySQL
MySQL Database
MySQL functions
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

ASKED: October 14, 2010  7:51 PM
UPDATED: October 15, 2010  6:04 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

———————–

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Iamjeannie
    wOW , THANK YOU !
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following