SQL: Select a product based on minimum value of key-CodePudding

I'm looking for a way to select Category with lowest CustKey value as seen in below table 1. I want it to be displayed in a column called SignupCategory. I have also linked to my current SQL code which I cant make display the Category rather than the CustKey. I appreciate any suggestions as I am terribly stuck atm. Code is semi-dummy code. Note: Given that I have 10.000 CustomerIDs I would want all 10.000 customers SignupCategory.

Table 1:

| CustKey | CustomerID | Category |
|---------|------------|----------|
| 1       | Cust1      | Paying   |
| 2       | Cust1      | Unpaying |
| 3       | Cust1      | Barred   |

Result should show SignupCategory 'Paying'

SQL Code:

Select c.AgreementNumber, SignupCategory

FROM Customer c

Following is the WIP from another thread I found on stackoverflow:

INNER JOIN
    (SELECT AgreementNumber, MIN(CustKey) As SignupCategory
    FROM Customer
    GROUP BY AgreementNumber, Category) X
ON c.AgreementNumber = X.AgreementNumber and c.Category = TRY_CONVERT(nvarchar,X.SignupCategory)

Following code works but displays CustKey (similar to what I found on stackoverflow):

INNER JOIN
    (SELECT AgreementNumber, MIN(CustKey) As SignupCategory
    FROM Customer
    GROUP BY AgreementNumber) X
ON c.AgreementNumber = X.AgreementNumber AND c.CustKey = X.SignupCategory

CodePudding user response：

For all customers respectively and if you have huge amount of data then use EXISTS instead of IN:-

SELECT category as SignupCategory FROM Customer WHERE CustKey IN (SELECT MIN(CustKey) FROM Customer group by CustomerID);