Search code examples
ms-access-2003

Select many columns based on lowest value from multiple columns (ms access)

I searched through google but all in vain, might be due to my search query.

I have a table like given below

------------------------------------------------------------
| LocalID | ASIN      | Price | Shipping | Total | Currancy |
------------------------------------------------------------
| 1       |B01MSMCN13 | 95.99 | 3        | 98.99 |  GBP     |
------------------------------------------------------------
| 2       |B01MSMCN13 | 100   | 0        | 100.0 |  GBP     |
------------------------------------------------------------
| 3       |B01MSMCN13 | 104.71| 9.61     | 114.32|  GBP     |
------------------------------------------------------------
| 4       |B01MSMCN13 | 124.81| 8.95     | 133.76|  GBP     |
------------------------------------------------------------
| 5       |B073WDMZVT | 139.99| 0        | 139.99|  GBP     |
------------------------------------------------------------
| 6       |B073WDMZVT | 159   | 0        | 159.0 |  GBP     |
------------------------------------------------------------
| 7       |B073WDMZVT | 179   | 0        | 179   |  GBP     |
------------------------------------------------------------

Problem:

I want to take all columns based on the lowest price + shipping

The Result I want like this

------------------------------------------------------------
| LocalID | ASIN      | Price | Shipping | Total | Currancy |
------------------------------------------------------------
| 1       |B01MSMCN13 | 95.99 | 3        | 98.99 |  GBP     |
------------------------------------------------------------
| 5       |B073WDMZVT | 139.99| 0        | 139.99|  GBP     |
------------------------------------------------------------

I have tried many queries but couldn't get the desired results.

Your help will be highly appreciated.

Solution

  • Use min func and group by

    SELECT * 
FROM Table t
WHERE Total = (SELECT MIN(Total)
               FROM Table t2
               WHERE t.ASIN = t2.ASIN
               GROUP BY ASIN)