June 11, 2012 at 11:04 am
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
June 11, 2012 at 11:29 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply