Inner Select Statement

Question

Hi

I have below code . In inner statement there are multiple reords. I want to get value of 1 record . How it can be done i don't use Max Keyword.

Thanks

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

Tuhin Paul · Answer

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.

Nidhi Kumari · Answer

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;

franklee · Answer

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!

Nakhatra · Answer

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

Jignesh Kumar · Answer

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;