CODEWITHSUNDEEP
ms-access
Mehboob Khan Afridi
Mehboob Khan Afridi

Reputation: 13

Flag the minimum value in each set of records

I am stuck in marking the minimum value in each set of records. I have a table like one structured below.

+-------+--------+----------+
|Prd ID | Price  | isLowest |
+---------------------------+
|00012  | 5      |          |
+---------------------------+
|00012  | 10     |          |
+---------------------------+
|00012  | 15     |          |
+---------------------------+
|00012  | 20     |          |
+---------------------------+
|00222  | 50     |          |
+---------------------------+
|00222  | 60     |          |
+---------------------------+
|00222  | 70     |          |
+---------------------------+

I want to have "Yes" in the isLowest column for each set of records. I mean by set of records as Prd ID is same i.e 00012 and 00222. I want the following output

+-------+--------+----------+
|Prd ID | Price  | isLowest |
+---------------------------+
|00012  | 5      |  Yes     |
+---------------------------+
|00012  | 10     |          |
+---------------------------+
|00012  | 15     |          |
+---------------------------+
|00012  | 20     |          |
+---------------------------+
|00222  | 50     |  Yes     |
+---------------------------+
|00222  | 60     |          |
+---------------------------+
|00222  | 70     |          |
+---------------------------+

I can get the minimum values for each set of Prd IDs however, I can't flag this in the iswinner column

To get the minimum values I use the following query.

SELECT tbl.prdid, Min(tbl.price) AS Price
FROM tbl
GROUP BY tbl.prdid

Upvotes: 1

Views: 164

Answers (1)

HansUp
HansUp

Reputation: 97101

Reconsider whether you actually need isLowest as a field in the table. You can easily determine its value with a query whenever you need to see it.

SELECT
    t.prdid,
    t.price,
    IIf(t.Price = sub.MinPrice, 'Yes', '') AS [isLowest]
FROM
    tbl AS t
    INNER JOIN
    (
        SELECT tbl.prdid, Min(tbl.price) AS MinPrice
        FROM tbl
        GROUP BY tbl.prdid
    ) AS sub
    ON t.prdid = sub.prdid;

Upvotes: 1

Related Questions