CODEWITHSUNDEEP
mysqlsql
Anders Friis
Anders Friis

Reputation: 190

Compare two MySQL tables and combine maximum rows to a new table

I've got two tables using the following simple structure:

-----------
id | count|
-----------

One table for men and another for females. I'd like to compare the rows of the two tables and select the ones that have the highest count. For example:

If one row has a count of 500, and the other row has a count of 200, it should select the first row, and add it to a new table along with the results of the rest of the rows.

Upvotes: 0

Views: 101

Answers (1)

TCFDS
TCFDS

Reputation: 622

This assumes that table_a and table_b have identical id's:

INSERT INTO new_table (id, count)
SELECT table_a.id, GREATEST(table_a.count, table_b.count)
FROM table_a
LEFT JOIN table_b
ON table_a.id = table_b.id

Upvotes: 1

Related Questions