MySQL query problematic

Question

I'm using PHP/MySQL and I have to create a new table from an existing one. Here's the problematic:

Table1:

photo_id   email          name_to   creation_date    link
1          [email protected]   paul      2012-11-21       link1.com
2          [email protected]   mark      2012-11-22       link2.com
3          [email protected]   alex      2012-11-23       link3.com
4          [email protected]   saul      2012-11-25       link4.com
5          [email protected]   john      2012-11-26       link5.com
6          [email protected]   math      2012-11-27       link6.com
7          [email protected]   fred      2012-11-28       link7.com

In Table1 the email is not unique, it can be repeated several times. Each link is different. With this data, I have to create a new table in which the email is unique with maximum of 3 links if there's more entries of one email (if so I need the data of the 3 latest entries).

So, in this case, Table2 would be like:

email           link1        creation_date1    name_to1    link2        creation_date2    name_to2    link3        creation_date3    name_to3
[email protected]    link5.com    2012-11-26        john        link4.com    2012-11-25        saul        link3.com    2012-11-23        alex
[email protected]    link6.com    2012-11-27        math        link2.com    2012-11-22        mark
[email protected]    link7.com    2012-11-28        fred

I know the GROUP_CONCAT feature but it's not really what I need here since the links would all be in the same column. Is it better to make a SELECT * FROM table1 and process the result into PHP arrays and after that create Table2 or a unique MySQL query would do the trick? Or create multiple MySQL tables?

Table1 is over 10 millions rows.

Any advice would be appreciate.

Thanks.

Yes I'm using PHP/MySQL. The important part here is the MySQL query but is it better to do the process in PHP, that's one of my interrogations. — Leo Stein
– Leo Stein, Commented Nov 28, 2012 at 18:23
You could consider just taking the first 3 rows of each email address and sort them in descending order. That would give you the three newest links. You could then add that data into the new table. This is not one master SQL query solution, but I think its easier if you split up the steps with scripting. — thatidiotguy
– thatidiotguy, Commented Nov 28, 2012 at 18:25

thatidiotguy · Accepted Answer · 2012-11-28 18:26:12Z

1

1) select all unique emails.

2) For each email, take the first 3 rows with that email ordered by creation_date descending.

3) Use that data to insert into new table.

Whatchu think?

answered Nov 28, 2012 at 18:26

thatidiotguy

9,01114 gold badges66 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leo Stein Over a year ago

Yes, not sure if it's the most efficient way but it works. Thanks!

thatidiotguy Over a year ago

Yeah sorry, I am not a huge SQL guru, that would know how to do that in all SQL queries.

Collectives™ on Stack Overflow

MySQL query problematic

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related