MIn qty by product_id (logic help)

Question

MIn qty by product_id (logic help)

twdavis-893252

Ten Centuries

Points: 1195
More actions
June 11, 2012 at 11:04 am

#262263

I have this query:
SELECT INV_ITEM_ID,SUM(QTY_AVAILABLE)AS QTY,PRODUCT_ID FROM PS_PRODKIT_AVL_VW
WHERE PRODUCT_ID IN('10-5266SP','10-3012SP','10-5202SP','10-2012SP','10-3033SP','10-5210SP','10-2066SP','10-3066SP','10-5212SP')
GROUP BY INV_ITEM_ID,PRODUCT_ID
ORDER BY PRODUCT_ID,QTY,INV_ITEM_ID
that gives this result:
INV_ITEM_IDQTYPRODUCT_ID
10-2002204.0000000000000010-2012SP
10-2005256.0000000000000010-2012SP
10-2009266.0000000000000010-2012SP
10-2028268.0000000000000010-2012SP
10-2024286.0000000000000010-2012SP
10-20BRQ287.0000000000000010-2012SP
10-2012311.0000000000000010-2012SP
10-20BRK72.0000000000000010-2066SP
10-206773.0000000000000010-2066SP
10-2066108.0000000000000010-2066SP
10-2005256.0000000000000010-2066SP
10-2009266.0000000000000010-2066SP
10-2028268.0000000000000010-2066SP
10-2002408.0000000000000010-2066SP
10-3024103.0000000000000010-3012SP
10-3012206.0000000000000010-3012SP
10-3002206.5000000000000010-3012SP
10-3009221.0000000000000010-3012SP
10-3005262.0000000000000010-3012SP
10-3028303.0000000000000010-3012SP
10-303359.0000000000000010-3033SP
10-303493.0000000000000010-3033SP
10-30BRT101.0000000000000010-3033SP
10-3009221.0000000000000010-3033SP
10-3005262.0000000000000010-3033SP
10-3028303.0000000000000010-3033SP
10-3002413.0000000000000010-3033SP
10-30BRK90.0000000000000010-3066SP
10-306695.0000000000000010-3066SP
10-3067132.0000000000000010-3066SP
10-3002206.5000000000000010-3066SP
10-3009221.0000000000000010-3066SP
10-3005262.0000000000000010-3066SP
10-3028303.0000000000000010-3066SP
10-5205317.0000000000000010-5202SP
10-52BRQ465.0000000000000010-5202SP
10-5212483.0000000000000010-5202SP
10-5224499.0000000000000010-5202SP
10-5202737.0000000000000010-5202SP
10-5210156.0000000000000010-5210SP
10-5209340.0000000000000010-5210SP
10-5202368.5000000000000010-5210SP
10-5228397.0000000000000010-5210SP
10-52BRQ465.0000000000000010-5210SP
10-5212483.0000000000000010-5210SP
10-5224499.0000000000000010-5210SP
10-52BRQ465.0000000000000010-5212SP
10-5212483.0000000000000010-5212SP
10-5224499.0000000000000010-5212SP
10-52660.0000000000000010-5266SP
10-52BRK1.0000000000000010-5266SP
10-526718.0000000000000010-5266SP
10-5210156.0000000000000010-5266SP
10-5209340.0000000000000010-5266SP
10-5202368.5000000000000010-5266SP
10-5228397.0000000000000010-5266SP
What I want to see is just the Minimal qty value for each group(Product_id)
what I want to see
INV_ITEM_IDQTYPRODUCT_ID
10-200220410-2012SP
10-20BRK7210-2066SP
10-302410310-3012SP
10-30335910-3033SP
10-30BRK9010-3066SP
10-520531710-5202SP
10-521015610-5210SP
10-52BRQ46510-5212SP
10-5266010-5266SP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

twdavis-893252 (6/11/2012)
I have this query:
SELECT INV_ITEM_ID,SUM(QTY_AVAILABLE)AS QTY,PRODUCT_ID FROM PS_PRODKIT_AVL_VW
WHERE PRODUCT_ID IN('10-5266SP','10-3012SP','10-5202SP','10-2012SP','10-3033SP','10-5210SP','10-2066SP','10-3066SP','10-5212SP')
GROUP BY INV_ITEM_ID,PRODUCT_ID
ORDER BY PRODUCT_ID,QTY,INV_ITEM_ID
that gives this result:
INV_ITEM_IDQTYPRODUCT_ID
10-2002204.0000000000000010-2012SP
10-2005256.0000000000000010-2012SP
10-2009266.0000000000000010-2012SP
10-2028268.0000000000000010-2012SP
10-2024286.0000000000000010-2012SP
10-20BRQ287.0000000000000010-2012SP
10-2012311.0000000000000010-2012SP
10-20BRK72.0000000000000010-2066SP
10-206773.0000000000000010-2066SP
10-2066108.0000000000000010-2066SP
10-2005256.0000000000000010-2066SP
10-2009266.0000000000000010-2066SP
10-2028268.0000000000000010-2066SP
10-2002408.0000000000000010-2066SP
10-3024103.0000000000000010-3012SP
10-3012206.0000000000000010-3012SP
10-3002206.5000000000000010-3012SP
10-3009221.0000000000000010-3012SP
10-3005262.0000000000000010-3012SP
10-3028303.0000000000000010-3012SP
10-303359.0000000000000010-3033SP
10-303493.0000000000000010-3033SP
10-30BRT101.0000000000000010-3033SP
10-3009221.0000000000000010-3033SP
10-3005262.0000000000000010-3033SP
10-3028303.0000000000000010-3033SP
10-3002413.0000000000000010-3033SP
10-30BRK90.0000000000000010-3066SP
10-306695.0000000000000010-3066SP
10-3067132.0000000000000010-3066SP
10-3002206.5000000000000010-3066SP
10-3009221.0000000000000010-3066SP
10-3005262.0000000000000010-3066SP
10-3028303.0000000000000010-3066SP
10-5205317.0000000000000010-5202SP
10-52BRQ465.0000000000000010-5202SP
10-5212483.0000000000000010-5202SP
10-5224499.0000000000000010-5202SP
10-5202737.0000000000000010-5202SP
10-5210156.0000000000000010-5210SP
10-5209340.0000000000000010-5210SP
10-5202368.5000000000000010-5210SP
10-5228397.0000000000000010-5210SP
10-52BRQ465.0000000000000010-5210SP
10-5212483.0000000000000010-5210SP
10-5224499.0000000000000010-5210SP
10-52BRQ465.0000000000000010-5212SP
10-5212483.0000000000000010-5212SP
10-5224499.0000000000000010-5212SP
10-52660.0000000000000010-5266SP
10-52BRK1.0000000000000010-5266SP
10-526718.0000000000000010-5266SP
10-5210156.0000000000000010-5266SP
10-5209340.0000000000000010-5266SP
10-5202368.5000000000000010-5266SP
10-5228397.0000000000000010-5266SP
What I want to see is just the Minimal qty value for each group(Product_id)
what I want to see
INV_ITEM_IDQTYPRODUCT_ID
10-200220410-2012SP
10-20BRK7210-2066SP
10-302410310-3012SP
10-30335910-3033SP
10-30BRK9010-3066SP
10-520531710-5202SP
10-521015610-5210SP
10-52BRQ46510-5212SP
10-5266010-5266SP

I took the time to make your data usable. The following should help you solve your problem.

WITH SampleData (INV_ITEM_ID,QTY,PRODUCT_ID) AS (

SELECT '10-2002', 204.00000000000000, '10-2012SP' UNION ALL

SELECT '10-2005', 256.00000000000000, '10-2012SP' UNION ALL

SELECT '10-2009', 266.00000000000000, '10-2012SP' UNION ALL

SELECT '10-2028', 268.00000000000000, '10-2012SP' UNION ALL

SELECT '10-2024', 286.00000000000000, '10-2012SP' UNION ALL

SELECT '10-20BRQ', 287.00000000000000, '10-2012SP' UNION ALL

SELECT '10-2012', 311.00000000000000, '10-2012SP' UNION ALL

SELECT '10-20BRK', 72.00000000000000, '10-2066SP' UNION ALL

SELECT '10-2067', 73.00000000000000, '10-2066SP' UNION ALL

SELECT '10-2066', 108.00000000000000, '10-2066SP' UNION ALL

SELECT '10-2005', 256.00000000000000, '10-2066SP' UNION ALL

SELECT '10-2009', 266.00000000000000, '10-2066SP' UNION ALL

SELECT '10-2028', 268.00000000000000, '10-2066SP' UNION ALL

SELECT '10-2002', 408.00000000000000, '10-2066SP' UNION ALL

SELECT '10-3024', 103.00000000000000, '10-3012SP' UNION ALL

SELECT '10-3012', 206.00000000000000, '10-3012SP' UNION ALL

SELECT '10-3002', 206.50000000000000, '10-3012SP' UNION ALL

SELECT '10-3009', 221.00000000000000, '10-3012SP' UNION ALL

SELECT '10-3005', 262.00000000000000, '10-3012SP' UNION ALL

SELECT '10-3028', 303.00000000000000, '10-3012SP' UNION ALL

SELECT '10-3033', 59.00000000000000, '10-3033SP' UNION ALL

SELECT '10-3034', 93.00000000000000, '10-3033SP' UNION ALL

SELECT '10-30BRT', 101.00000000000000, '10-3033SP' UNION ALL

SELECT '10-3009', 221.00000000000000, '10-3033SP' UNION ALL

SELECT '10-3005', 262.00000000000000, '10-3033SP' UNION ALL

SELECT '10-3028', 303.00000000000000, '10-3033SP' UNION ALL

SELECT '10-3002', 413.00000000000000, '10-3033SP' UNION ALL

SELECT '10-30BRK', 90.00000000000000, '10-3066SP' UNION ALL

SELECT '10-3066', 95.00000000000000, '10-3066SP' UNION ALL

SELECT '10-3067', 132.00000000000000, '10-3066SP' UNION ALL

SELECT '10-3002', 206.50000000000000, '10-3066SP' UNION ALL

SELECT '10-3009', 221.00000000000000, '10-3066SP' UNION ALL

SELECT '10-3005', 262.00000000000000, '10-3066SP' UNION ALL

SELECT '10-3028', 303.00000000000000, '10-3066SP' UNION ALL

SELECT '10-5205', 317.00000000000000, '10-5202SP' UNION ALL

SELECT '10-52BRQ', 465.00000000000000, '10-5202SP' UNION ALL

SELECT '10-5212', 483.00000000000000, '10-5202SP' UNION ALL

SELECT '10-5224', 499.00000000000000, '10-5202SP' UNION ALL

SELECT '10-5202', 737.00000000000000, '10-5202SP' UNION ALL

SELECT '10-5210', 156.00000000000000, '10-5210SP' UNION ALL

SELECT '10-5209', 340.00000000000000, '10-5210SP' UNION ALL

SELECT '10-5202', 368.50000000000000, '10-5210SP' UNION ALL

SELECT '10-5228', 397.00000000000000, '10-5210SP' UNION ALL

SELECT '10-52BRQ', 465.00000000000000, '10-5210SP' UNION ALL

SELECT '10-5212', 483.00000000000000, '10-5210SP' UNION ALL

SELECT '10-5224', 499.00000000000000, '10-5210SP' UNION ALL

SELECT '10-52BRQ', 465.00000000000000, '10-5212SP' UNION ALL

SELECT '10-5212', 483.00000000000000, '10-5212SP' UNION ALL

SELECT '10-5224', 499.00000000000000, '10-5212SP' UNION ALL

SELECT '10-5266', 0.00000000000000, '10-5266SP' UNION ALL

SELECT '10-52BRK', 1.00000000000000, '10-5266SP' UNION ALL

SELECT '10-5267', 18.00000000000000, '10-5266SP' UNION ALL

SELECT '10-5210', 156.00000000000000, '10-5266SP' UNION ALL

SELECT '10-5209', 340.00000000000000, '10-5266SP' UNION ALL

SELECT '10-5202', 368.50000000000000, '10-5266SP' UNION ALL

SELECT '10-5228', 397.00000000000000, '10-5266SP'

),

BaseData AS (

SELECT

INV_ITEM_ID,

QTY,

PRODUCT_ID,

ROW_NUMBER() OVER (PARTITION BY Product_ID ORDER BY QTY ASC) rn

FROM

SampleData

)

SELECT

INV_ITEM_ID,

QTY,

PRODUCT_ID

FROM

BaseData

WHERE

rn = 1;

For future questions, you may want to take the time to read and follow the instructions in the first article I reference below in my signature block. It will walk you through the what and how on posting the information we need to help you. What you provided worked, but it took extra effort to provide you an answer.