create MAX table: include additional column value

[ChenXiu]

2 mySQL tables have the same 3 columns: "Color" "Price" and "sku_number."

To create a "maximum price" table from those tables, this works great:

create table highestTable as
SELECT * , MAX(Price) FROM (
SELECT * FROM table1 UNION ALL SELECT * FROM table2)
both_tables GROUP BY sku_number; 

However, the newly created MAX table only has colors from table1.

How can I make the newly created MAX table have the color from the table with the highest Price?
 

[Barand]

Why have you got 2 identically structured tables?

[edit...]

Why are creating a third table consisting of data derived from other tables?

 

When you use an aggregation function the only values you can rely on on are the aggregated values and any GROUP BY values. All other values that are selected will be arbitrarily chosen from any record in the group.

[Barand]

The tactic to use in this situation is

• create a subquery to get the max prices
• Join your original data to the subquery on the max price so you list those records that match the price.

                     SELECT both_tables.sku_number
                          , both_tables.color
                          , both_tables.price
                     FROM (
                         SELECT * FROM table1
                         UNION ALL
                         SELECT * FROM table2
                     ) both_tables
                     JOIN
                     (
                         SELECT sku_number  
                              , MAX(Price) as price
                         FROM (
                            SELECT sku_number, price FROM table1 
                            UNION ALL 
                            SELECT sku_number, price FROM table2
                            ) both_prices 
                         GROUP BY sku_number
                     ) max_prices USING (sku_number, price)

 

[ChenXiu]

Both tables are supplied to me every morning, exact same format, and primary key is sku_number.
I create my own table based on the best price between the 2 tables.
However, even though sku numbers match, there are always slight color variations.

Example:
table1
| sku12345 | beige | $6.00 |
| sku99999 | pink | $44.00 |
table2
| sku12345 | tan | $15.00 |
| sku99999 | red | $30.00 |
| sku50505 | orange | $7.00 |

Currently my max price code gives me this:
| sku12345 | beige | $15.00 |  <--- but should be "tan" because max price is from table2
| sku99999 | pink | $44.00 |
| sku50505 | orange | $7.00 |

( OOPS.... I see you posted code while I was typing my answer 😀 )
Should I try your code now? Or might my answer changed your response?

[ChenXiu]

I just now tried your code, and it works!

For my own learning purposes, may I please complicate things a bit? 😀
Let's say I have 2 tables:

Table1
Company | Description |  sku_number |  Price
ACME       | Recliner        | sku_111111      | $10.00
ACME       | Table            | sku_22222     | $7.00

Table2
Company | Description | sku_number | Price
ZUNFXZ    | Lounger       | sku_111111      | $50.00
ZUNFXZ    | Bench          | sku_22222     | $3.00

I like how the following code *always* uses the descriptions from Table1, even when price is higher in Table2

create table highestTable as
SELECT * , MAX(Price) FROM (
SELECT * FROM table1 UNION ALL SELECT * FROM table2)
both_tables GROUP BY sku_number; 

BUT... I want the resulting MAX(Price) table to reflect the value of the "Company" column where the MAX(Price) came from.
Like this:
MaxPriceTable
Company | Description | SKU_number | Price
ZUNFXZ   | Recliner        | sku_111111      | $50.00 <--- uses description from Table1, and shows Company that MAX(Price) came from
ACME       | Table            | sku_22222     | $3.00 <--- uses description from Table1, and shows Company that MAX(Price) came from

Since the above code already does everything I want, is there a modification to the code that yields the Company that the MAX(Price) came from, while leaving all other columns untouched?

 

[Barand]

You need to lose the $ signs from your prices and store in  numeric type column, such as DECIMAL(10,2). If they are stored as varchar() the sort order will be alphabetical instead of numeric

For example

mysql> select * from table3 order by price;
+------------+--------+--------+
| sku_number | color  | price  |
+------------+--------+--------+
| sku12345   | tan    | $15.00 |
| sku99999   | red    | $5.00  |
| sku50505   | orange | $7.00  |
+------------+--------+--------+

mysql> select max(price) from table3;
+------------+
| max(price) |
+------------+
| $7.00      |
+------------+

 

23 minutes ago, ChenXiu said:

I like how the following code *always* uses the descriptions from Table1, even when price is higher in Table2

As I told you earlier, values other than sku (group by) and price (aggregated) are arbitrary (If you get what you want it's by luck not design.)

[Barand]

This time I have appended table1 and table2 data into a single table (table3)

+---------+------------+--------+-------+
| company | sku_number | color  | price |
+---------+------------+--------+-------+
| ACME    | sku12345   | beige  |  6.00 |
| ACME    | sku99999   | pink   | 44.00 |
| ZUNFXZ  | sku12345   | tan    | 15.00 |
| ZUNFXZ  | sku50505   | orange |  7.00 |
| ZUNFXZ  | sku99999   | red    | 30.00 |
+---------+------------+--------+-------+

The query simplifies (no unions) to

SELECT  t3.company
      , t3.sku_number
      , t3.color
      , t3.price
 FROM table3 t3
 JOIN
 (
     SELECT sku_number  
          , MAX(Price) as price
     FROM table3
     GROUP BY sku_number
 ) max_prices USING (sku_number, price);

+---------+------------+--------+-------+
| company | sku_number | color  | price |
+---------+------------+--------+-------+
| ACME    | sku99999   | pink   | 44.00 |
| ZUNFXZ  | sku12345   | tan    | 15.00 |
| ZUNFXZ  | sku50505   | orange |  7.00 |
+---------+------------+--------+-------+

NOTE: Table 3 primary key is (sku_number,company) to avoid duplicates