Revisions to How to limit the number of rows in select statement for a condition in sql server 2012

added 605 characters in body

Source Link

edited Jun 28, 2017 at 13:01

23.9k
11
50
89

CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_list_price_eachv_price_book 
             WHEREINNER JOIN   skh.v_price_bookICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) lpepb

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEMpb.ITEMNO AS product_code,
            skh.ICITEMpb.CNTLACCT,
            lpepb.price AS list_price,
            skh.v_price_bookpb.price,
            lpepb.unit AS Expr2,
            skh.v_price_bookpb.price_unit
FROM        skh.ARCUS
INNERCROSS JOINAPPLY (SELECT skh.v_price_bookTOP 1 *  --<< add required fields here don't use *
ON          skh.ARCUS.PRICLIST =  FROM         skh.v_price_book.price_list 
             INNER JOIN   skh.ICITEM 
             ON           skh.v_price_bookICITEM.item_shortITEMNO = skh.ICITEMpb.ITEMNOitem_short
             AND          skh.v_price_bookICITEM.companyAUDTORG = skh.ICITEMpb.AUDTORGcompany
CROSS APPLY (SELECT TOP 1 *        INNER JOIN   skh.v_list_price_each 
             FROMON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.item_shortprice_list = skh.v_list_price_eachARCUS.item_short) lpePRICLIST
WHERE             AND          skh.ICITEM.INACTIVE = 0
AND) pb
WHERE        skh.ARCUS.SWACTV = 1
--ORDER BY    skh.ARCUS.IDCUST;IDCUST

As it seems you're using this query within a VIEW (TOP (100) PERCENT), I'd suggest you to remove TOP and move ORDER BY to your SELECT view_name statement.

CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEM.ITEMNO AS product_code,
            skh.ICITEM.CNTLACCT,
            lpe.price AS list_price,
            skh.v_price_book.price,
            lpe.unit AS Expr2,
            skh.v_price_book.price_unit
FROM        skh.ARCUS
INNER JOIN  skh.v_price_book 
ON          skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN  skh.ICITEM 
ON          skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND         skh.v_price_book.company = skh.ICITEM.AUDTORG
CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe
WHERE       skh.ICITEM.INACTIVE = 0
AND         skh.ARCUS.SWACTV = 1
ORDER BY    skh.ARCUS.IDCUST;

CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_price_book 
             INNER JOIN   skh.ICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) pb

SELECT      skh.ARCUS.IDCUST,
            pb.ITEMNO AS product_code,
            pb.CNTLACCT,
            pb.price AS list_price,
            pb.price,
            pb.unit AS Expr2,
            pb.price_unit
FROM        skh.ARCUS
CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_price_book 
             INNER JOIN   skh.ICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) pb
WHERE        skh.ARCUS.SWACTV = 1
--ORDER BY    skh.ARCUS.IDCUST

As it seems you're using this query within a VIEW (TOP (100) PERCENT), I'd suggest you to remove TOP and move ORDER BY to your SELECT view_name statement.

added 3 characters in body

Source Link

edited Jun 28, 2017 at 9:23

McNets

23.9k
11
50
89

If you need to limit the number of rowrows returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe

Due you're using a TOP (X) expression, you could add an ORDER BY clause tointo the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEM.ITEMNO AS product_code,
            skh.ICITEM.CNTLACCT,
            lpe.price AS list_price,
            skh.v_price_book.price,
            lpe.unit AS Expr2,
            skh.v_price_book.price_unit
FROM        skh.ARCUS
INNER JOIN  skh.v_price_book 
ON          skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN  skh.ICITEM 
ON          skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND         skh.v_price_book.company = skh.ICITEM.AUDTORG
CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe
WHERE       skh.ICITEM.INACTIVE = 0
AND         skh.ARCUS.SWACTV = 1
ORDER BY    skh.ARCUS.IDCUST;

If you need to limit the number of row returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe

Due you're using a TOP (X) expression, you could add an ORDER BY clause to the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEM.ITEMNO AS product_code,
            skh.ICITEM.CNTLACCT,
            lpe.price AS list_price,
            skh.v_price_book.price,
            lpe.unit AS Expr2,
            skh.v_price_book.price_unit
FROM        skh.ARCUS
INNER JOIN  skh.v_price_book 
ON          skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN  skh.ICITEM 
ON          skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND         skh.v_price_book.company = skh.ICITEM.AUDTORG
CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe
WHERE       skh.ICITEM.INACTIVE = 0
AND         skh.ARCUS.SWACTV = 1
ORDER BY    skh.ARCUS.IDCUST;

If you need to limit the number of rows returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe

Due you're using a TOP (X) expression, you could add an ORDER BY clause into the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEM.ITEMNO AS product_code,
            skh.ICITEM.CNTLACCT,
            lpe.price AS list_price,
            skh.v_price_book.price,
            lpe.unit AS Expr2,
            skh.v_price_book.price_unit
FROM        skh.ARCUS
INNER JOIN  skh.v_price_book 
ON          skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN  skh.ICITEM 
ON          skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND         skh.v_price_book.company = skh.ICITEM.AUDTORG
CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe
WHERE       skh.ICITEM.INACTIVE = 0
AND         skh.ARCUS.SWACTV = 1
ORDER BY    skh.ARCUS.IDCUST;

Source Link

created Jun 28, 2017 at 7:11

McNets

23.9k
11
50
89

If you need to limit the number of row returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe

Due you're using a TOP (X) expression, you could add an ORDER BY clause to the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            skh.ICITEM.ITEMNO AS product_code,
            skh.ICITEM.CNTLACCT,
            lpe.price AS list_price,
            skh.v_price_book.price,
            lpe.unit AS Expr2,
            skh.v_price_book.price_unit
FROM        skh.ARCUS
INNER JOIN  skh.v_price_book 
ON          skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN  skh.ICITEM 
ON          skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND         skh.v_price_book.company = skh.ICITEM.AUDTORG
CROSS APPLY (SELECT TOP 1 *
             FROM   skh.v_list_price_each
             WHERE  skh.v_price_book.item_short = skh.v_list_price_each.item_short) lpe
WHERE       skh.ICITEM.INACTIVE = 0
AND         skh.ARCUS.SWACTV = 1
ORDER BY    skh.ARCUS.IDCUST;

Stack Exchange Network

Return to Answer