Inner Select Statement

You can use a TOP 1 clause to get a single record without using MAX.

SELECT ColumnName FROM ( SELECT TOP 1 ColumnName FROM TableName WHERE Condition ORDER BY ColumnToOrder ) AS InnerQuery;

I’ve come across sites like SCMAPK.com that share similar tech tips—worth checking out. Let me know if you need more help!

We can also use the Row_Number() function here:
 

WITH RankedRates AS (
    SELECT
        T2."CardCode",
        I0."Rate",
        ROW_NUMBER() OVER (
            PARTITION BY T2."CardCode"
            ORDER BY I0."Rate" DESC  -- Assuming you want the highest rate
        ) AS RowNum
    FROM
        OPCH T2
        INNER JOIN PCH9 I1 ON T2."DocEntry" = I1."DocEntry"
        INNER JOIN DPO5 I0 ON I1."BaseAbs" = I0."AbsEntry"
)
SELECT
    "CardCode",
    "Rate" AS MaxRate
FROM
    RankedRates
WHERE
    RowNum = 1;

In the ROW_NUMBER() approach, we use a Common Table Expression (CTE) to rank the rates for each CardCode. Then, we select the top-ranked rate for each CardCode.

jignesh is currect .But insted the fild he select from I0 . select top 1 I0.*  which missmatch the outer query select field.

select top 1 I0.* from DPO5 I0

Try this
 

SELECT
    T2."CardCode",
    (
        SELECT TOP 1 I0."Rate"
        FROM DPO5 I0
        WHERE I0."AbsEntry" = (
            SELECT TOP 1 I1."BaseAbs"
            FROM PCH9 I1
            WHERE I1."DocEntry" = T2."DocEntry"
        )
    ) AS Rate
FROM
    OPCH T2;

Hello Ramco,

You can do as below, Limit 1 or you can use Top 1 

SELECT
    T2."CardCode",
    (
        SELECT I0."Rate"
        FROM DPO5 I0
        WHERE I0."AbsEntry" = (
            SELECT I1."BaseAbs"
            FROM PCH9 I1
            WHERE I1."DocEntry" = T2."DocEntry"
            ORDER BY I1."BaseAbs" --Here you can do order by clause as per your requirement
            LIMIT 1
        )
    ) AS Rate
FROM
    OPCH T2;