基于LastTransactionDate为客户排名并标记父/子角色的SQL求助

First, let's walk through the issues with your existing code:

• Duplicate column name conflict: You're selecting the original Class_Profile column and also using the same name for your CASE expression result. Most SQL databases will throw an error here because duplicate column names aren't allowed in the SELECT list.
• Unnecessary GROUP BY: Since you mentioned each CustomerNo (and other columns) are unique, grouping by all columns doesn't change your result set—it just adds unnecessary overhead.
• Incorrect tagging logic: Your current query marks all customers with the latest LastTransactionDate as 'P', but you specifically want only the customer 0003178419452479 (with that max date) to be tagged as 'P', while all others are 'C'.

Solution 1: Explicitly Tag the Target Customer as 'P' #

If your goal is to only mark the specific CustomerNo = '0003178419452479' as 'P' (even if other customers share the same max transaction date), use this query:

SELECT 
    CustomerNO,
    CustomerID,
    CustomerIDType,
    CustomerNationalityCode,
    CustomerDateOfBirth,
    LastTransactionDate,
    -- Force rank 1 for the target customer, others get normal dense rank by transaction date
    CASE 
        WHEN CustomerNO = '0003178419452479' THEN 1
        ELSE DENSE_RANK() OVER (ORDER BY LastTransactionDate DESC)
    END AS ranks,
    -- Tag only the target customer as 'P', everyone else as 'C'
    CASE 
        WHEN CustomerNO = '0003178419452479' THEN 'P'
        ELSE 'C'
    END AS Class_Profile
FROM CustomerTabledetails
ORDER BY LastTransactionDate DESC;

Solution 2: Tag All Customers with the Max Transaction Date as 'P' #

If you later decide to mark all customers with the latest transaction date as 'P' (not just the specific one), use a CTE to first calculate ranks, then apply the tagging:

WITH RankedCustomers AS (
    SELECT 
        CustomerNO,
        CustomerID,
        CustomerIDType,
        CustomerNationalityCode,
        CustomerDateOfBirth,
        LastTransactionDate,
        DENSE_RANK() OVER (ORDER BY LastTransactionDate DESC) AS ranks
    FROM CustomerTabledetails
)
SELECT 
    *,
    CASE 
        WHEN ranks = 1 THEN 'P'
        ELSE 'C'
    END AS Class_Profile
FROM RankedCustomers
ORDER BY LastTransactionDate DESC;

Key Notes: #

• We removed the redundant GROUP BY clause since your customer records are already unique per CustomerNo.
• The first solution prioritizes your specific requirement to tag only 0003178419452479 as 'P', even if other customers have the same latest transaction date.
• The second solution is more flexible if you ever need to tag all top-date customers as parents.

内容的提问来源于stack exchange，提问作者sathish kumar