Dinesh Dinesh - 4 months ago 9
SQL Question

Getting minimum value for three columns group by a single column in a single table

I have a single table and my table structure is like this:

unique_id vendor_name price1 price2 price3 code

1 Vendor 1 0.0012 0.0014 0.0054 125
2 Vendor 2 0.0015 0.0016 0.0050 125
3 Vendor 3 0.0011 0.0019 0.0088 125
4 Vendor 1 0.0025 0.0024 0.0034 126
5 Vendor 2 0.0043 0.0019 0.0065 126
6 Vendor 3 0.0019 0.0085 0.0082 126


I have to fetch the min price for each price column group by code. And my expected output is as follows:

Code price1 price2 price3 vendor for price1 vendor for price 2 vendor for price 3

125 0.0011 0.0014 0.0050 Vendor3 Vendor1 Vendor 2
126 0.0019 0.0019 0.0034 Vendor3 Vendor2 Vendor 1


So what would be the MySQL query to fetch records like this? And also I have to make a query to fetch maximum and second highest value from the table and there may be any number of rows with single code.

My data is in this SQL Fiddle.

In the second highest value case the output should be as:

Code price1 price2 price3 vendor for price1 vendor for price 2 vendor for price 3
125 0.0012 0.0016 0.0054 Vendor1 Vendor2 Vendor 1
126 0.0025 0.0024 0.0065 Vendor1 Vendor1 Vendor 2

Answer
SELECT
    data.*,
    v1.vendor_name 'vendor for price1',
    v2.vendor_name 'vendor for price2',
    v3.vendor_name 'vendor for price3'
FROM
    (
        SELECT
            Code,
            MIN(price1) price1,
            MIN(price2) price2,
            MIN(price3) price3
        FROM
            tbl
        GROUP BY Code
    ) data
    LEFT JOIN
    (
        SELECT 
            MIN(vendor_name) vendor_name,
            Code
        FROM
            tbl
        WHERE
            price1 = 
            (
                SELECT
                    MIN(price1)
                FROM
                    tbl t
                WHERE
                    t.Code = tbl.Code
            )
        GROUP BY Code
    ) v1 ON data.Code = v1.Code
    LEFT JOIN
    (
        SELECT 
            MIN(vendor_name) vendor_name
            Code
        FROM
            tbl
        WHERE
            price2 = 
            (
                SELECT
                    MIN(price2)
                FROM
                    tbl t
                WHERE
                    t.Code = tbl.Code
            )
        GROUP BY Code
    ) v2 ON data.Code = v2.Code
    LEFT JOIN
    (
        SELECT 
            MIN(vendor_name) vendor_name
            Code
        FROM
            tbl
        WHERE
            price3 = 
            (
                SELECT
                    MIN(price3)
                FROM
                    tbl t
                WHERE
                    t.Code = tbl.Code
            )
        GROUP BY Code
    ) v3 ON data.Code = v3.Code

Even though the query itself looks pretty big, but the joins are repeated 3 times.

UPDATE I updated the query removing LIMIT and adding MIN(vendor_name) vendor_name instead of vendor_name.

Demo

Here

OUTPUT

| CODE | PRICE1 | PRICE2 | PRICE3 | VENDOR FOR PRICE1 | VENDOR FOR PRICE2 | VENDOR FOR PRICE3 |
-----------------------------------------------------------------------------------------------
|  125 | 0.0011 | 0.0014 | 0.0050 |          Vendor 3 |          Vendor 1 |          Vendor 2 |
|  126 | 0.0019 | 0.0019 | 0.0034 |          Vendor 3 |          Vendor 2 |          Vendor 1 |