locked
How to sort quantity and without quantity ? RRS feed

  • Question

  •  Hi friends


    Table name :Products

    Supplier_Id    Product_ID  Product_Type   Price      Quantity
    S101                P101              T101                  10                0
    S102                P101              T101                   5                 2
    S103                P101              T101                  15                5
    S102                P101              T102                  15               2
    S103                P101              T103                  18               0
    S102                P101              T103                  20               0


    I use following code for taking result :
    SELECT p.*        
    FROM   dbo.products p        
     LEFT    
      JOIN (        
            SELECT Min(price) As [min_price]        
                 , supplier_id  , product_type      
                 , product_id        
            FROM   dbo.products        
            WHERE  product_id = 'p101'   
            AND    quantity >= '1'     
            GROUP 
                BY supplier_id        
                 , product_id  , product_type     
           ) As [mins]        
        ON p.supplier_id = mins.supplier_id        
       AND p.product_id = mins.product_id        
       AND p.price = mins.min_price  AND p.product_type = mins.product_type
    WHERE  mins.min_price IS NOT NULL    
    OR     p.price = 0  

    Result is like this:
    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S102                P101              T101                  5                 2
    S102                P101              T102                  15               2


    I need , if quanity is not availabile , need result with zero quantity with less price
    (both quantity and with out quantity)


    The result i need like this :
    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S102                P101              T101                  5                 2
    S102                P101              T102                  15               2
    S103                P101              T103                  18               0   

    need your help ....
    ___________________________________________________________
    Alex
    Tuesday, January 13, 2009 8:12 AM

Answers

  • ---------------------------------     
    --  Author: liangCK 小梁     
    ---------------------------------     
          
    --> 生成测试数据: @Products     
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)     
    INSERT INTO @Products     
    SELECT 'S101','P101','T101',15,5 UNION ALL    
    SELECT 'S102','P101','T101',15,2 UNION ALL    
    SELECT 'S103','P101','T101',15,3 UNION ALL    
    SELECT 'S102','P101','T102',15,2 UNION ALL    
    SELECT 'S103','P101','T103',18,0 UNION ALL    
    SELECT 'S102','P101','T103',20,3     
        
    --SQL查询如下:     
     
     
    ;WITH Liang AS 
    (  
        SELECT *  
        FROM (  
            SELECT   
                *,  
                RID=ROW_NUMBER() OVER(PARTITION BY Product_ID,Product_Type ORDER BY Price,Quantity)  
            FROM @Products  
            WHERE Quantity>=1  
        ) AS T  
        WHERE RID=1  
    )  
    SELECT   
        Supplier_Id,  
        Product_ID,  
        Product_Type,  
        Price,  
        Quantity  
    FROM Liang  
    UNION ALL 
    SELECT   
        Supplier_Id,  
        Product_ID,  
        Product_Type,  
        Price,  
        Quantity  
    FROM (  
        SELECT   
            *,  
            RID=ROW_NUMBER() OVER(PARTITION BY Product_ID,Product_Type ORDER BY Price)  
        FROM @Products AS A  
        WHERE NOT EXISTS(     
                  SELECT *     
                  FROM Liang     
                  WHERE A.Product_ID=Product_ID     
                      AND A.Product_Type=Product_Type     
              )  
              AND Quantity<=1  
    AS B  
    WHERE RID=1  
    ORDER BY   
        Product_ID,  
        Product_Type  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S102        P101       T101         15          2  
    S102        P101       T102         15          2  
    S102        P101       T103         20          3  
     
    (3 行受影响)  
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Marked as answer by Alex Q8 Tuesday, January 13, 2009 10:15 AM
    Tuesday, January 13, 2009 10:02 AM

All replies

  •  
    ---------------------------------  
    --  Author: liangCK 小梁  
    ---------------------------------  
       
    --> 生成测试数据: @Products  
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)  
    INSERT INTO @Products  
    SELECT 'S101','P101','T101',10,0 UNION ALL 
    SELECT 'S102','P101','T101',5,2 UNION ALL 
    SELECT 'S103','P101','T101',15,5 UNION ALL 
    SELECT 'S102','P101','T102',15,2 UNION ALL 
    SELECT 'S103','P101','T103',18,0 UNION ALL 
    SELECT 'S102','P101','T103',20,0  
     
    --SQL查询如下:  
     
    SELECT 
        A.*  
    FROM @Products AS A  
        JOIN(  
            SELECT 
                Product_Id,  
                Product_Type,  
                MIN(Price) AS Price  
            FROM @Products   
            WHERE Product_ID='P101' 
            GROUP BY   
                Product_Id,  
                Product_Type  
        ) AS B  
            ON A.Product_Id=B.Product_Id  
                AND A.Product_Type=B.Product_Type  
                AND A.Price=B.Price  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S102        P101       T101         5           2  
    S102        P101       T102         15          2  
    S103        P101       T103         18          0  
     
    (3 行受影响)  
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Tuesday, January 13, 2009 8:26 AM
  • if the table update to quantity '3'

     Supplier_Id    Product_ID  Product_Type   Price      Quantity
    S101                P101              T101                  10                0
    S102                P101              T101                   5                 2
    S103                P101              T101                  15                5
    S102                P101              T102                  15               2
    S103                P101              T103                  18               0
    S102                P101              T103                  20               3

    The result i need like this :
    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S102                P101              T101                  5                 2
    S102                P101              T102                  15               2
    S103                P101              T103                  20               3   

    Your provided code is not accept this...


    Because,
    1) if quantity is there> take less price with less quantity (based on user request (quantity 1))
    2) if quantity is not there > take less price  product type.
    3) need distinct product type .



    Alex

    Tuesday, January 13, 2009 8:40 AM
  •  
    ---------------------------------  
    --  Author: liangCK 小梁  
    ---------------------------------  
       
    --> 生成测试数据: @Products  
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)  
    INSERT INTO @Products  
    SELECT 'S101','P101','T101',10,10 UNION ALL 
    SELECT 'S102','P101','T101',5,0 UNION ALL 
    SELECT 'S103','P101','T101',15,5 UNION ALL 
    SELECT 'S102','P101','T102',15,2 UNION ALL 
    SELECT 'S103','P101','T103',18,0 UNION ALL 
    SELECT 'S102','P101','T103',20,0  
     
    --SQL查询如下:  
     
    ;WITH Liang AS 
    (  
        SELECT   
            A.*  
        FROM @Products AS A  
            JOIN(  
                SELECT 
                    Product_ID,  
                    Product_Type,  
                    MIN(Price) AS Price  
                FROM @Products   
                WHERE Product_ID='P101' 
                    AND Quantity>=1  
                GROUP BY   
                    Product_ID,  
                    Product_Type  
            ) AS B  
                ON A.Product_Id=B.Product_Id     
                    AND A.Product_Type=B.Product_Type     
                    AND A.Price=B.Price     
    )  
    SELECT *   
    FROM Liang   
    UNION ALL 
    SELECT *  
    FROM @Products AS A  
    WHERE NOT EXISTS(  
              SELECT *  
              FROM Liang  
              WHERE A.Product_ID=Product_ID  
                  AND A.Product_Type=Product_Type  
          )  
          AND NOT EXISTS(  
                  SELECT *  
                  FROM @Products  
                  WHERE A.Product_ID=Product_ID  
                      AND A.Product_Type=Product_Type  
                      AND A.Price>Price  
              )  
          AND Quantity<=0  
    ORDER BY   
        Product_ID,  
        Product_Type  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S101        P101       T101         10          10  
    S102        P101       T102         15          2  
    S103        P101       T103         18          0  
     
    (3 行受影响)  
     
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Tuesday, January 13, 2009 8:47 AM
  •  
    ---------------------------------  
    --  Author: liangCK 小梁  
    ---------------------------------  
       
    --> 生成测试数据: @Products  
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)  
    INSERT INTO @Products  
    SELECT 'S101','P101','T101',10,0 UNION ALL 
    SELECT 'S102','P101','T101',5,2 UNION ALL 
    SELECT 'S103','P101','T101',15,5 UNION ALL 
    SELECT 'S102','P101','T102',15,2 UNION ALL 
    SELECT 'S103','P101','T103',18,0 UNION ALL 
    SELECT 'S102','P101','T103',20,3  
     
    --SQL查询如下:  
     
    ;WITH Liang AS 
    (  
        SELECT   
            A.*  
        FROM @Products AS A  
            JOIN(  
                SELECT 
                    Product_ID,  
                    Product_Type,  
                    MIN(Price) AS Price  
                FROM @Products   
                WHERE Product_ID='P101' 
                    AND Quantity>=1  
                GROUP BY   
                    Product_ID,  
                    Product_Type  
            ) AS B  
                ON A.Product_Id=B.Product_Id     
                    AND A.Product_Type=B.Product_Type     
                    AND A.Price=B.Price     
    )  
    SELECT *   
    FROM Liang   
    UNION ALL 
    SELECT *  
    FROM @Products AS A  
    WHERE NOT EXISTS(  
              SELECT *  
              FROM Liang  
              WHERE A.Product_ID=Product_ID  
                  AND A.Product_Type=Product_Type  
          )  
          AND NOT EXISTS(  
                  SELECT *  
                  FROM @Products  
                  WHERE A.Product_ID=Product_ID  
                      AND A.Product_Type=Product_Type  
                      AND A.Price>Price  
              )  
          AND Quantity<=0  
    ORDER BY   
        Product_ID,  
        Product_Type  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S102        P101       T101         5           2  
    S102        P101       T102         15          2  
    S102        P101       T103         20          3  
     
    (3 行受影响)  
     
     
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Tuesday, January 13, 2009 8:48 AM
  • Thanks, your provided code is good

    but one condition is remaining...

    Supplier_Id    Product_ID  Product_Type   Price      Quantity
    S101                P101              T101                  10                0
    S102                P101              T101                  15                 0
    S103                P101              T101                  15                5
    S102                P101              T102                  15               2
    S103                P101              T103                  18               0
    S102                P101              T103                  20               3  


    I update the coloured(       )Portion ..

    In this case, i get result like this
    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S102                P101              T101                  15                 0 --------------->---(T101 is coming duplicate, when price comes same)
    S103                P101              T101                  15                5
    S102                P101              T102                  15               2
    S103                P101              T103                  20              3


    But I need like this...

    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S103                P101              T101                  15                5
    S102                P101              T102                  15               2
    S103                P101              T103                  20              3


    Alex
    Tuesday, January 13, 2009 9:08 AM
  •  
    ---------------------------------  
    --  Author: liangCK 小梁  
    ---------------------------------  
       
    --> 生成测试数据: @Products  
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)  
    INSERT INTO @Products  
    SELECT 'S101','P101','T101',10,0 UNION ALL 
    SELECT 'S102','P101','T101',15,0 UNION ALL 
    SELECT 'S103','P101','T101',15,5 UNION ALL 
    SELECT 'S102','P101','T102',15,2 UNION ALL 
    SELECT 'S103','P101','T103',18,0 UNION ALL 
    SELECT 'S102','P101','T103',20,3  
     
    --SQL查询如下:  
     
    ;WITH Liang AS 
    (  
        SELECT   
            A.*  
        FROM @Products AS A  
        WHERE NOT EXISTS(  
                  SELECT *  
                  FROM @Products  
                  WHERE A.Product_ID=Product_ID  
                      AND A.Product_Type=Product_Type  
                      AND Price>A.Price  
              )  
              AND Quantity>=1  
              AND Product_ID='P101' 
    )  
    SELECT *   
    FROM Liang   
    UNION ALL 
    SELECT *  
    FROM @Products AS A  
    WHERE NOT EXISTS(  
              SELECT *  
              FROM Liang  
              WHERE A.Product_ID=Product_ID  
                  AND A.Product_Type=Product_Type  
          )  
          AND NOT EXISTS(  
                  SELECT *  
                  FROM @Products  
                  WHERE A.Product_ID=Product_ID  
                      AND A.Product_Type=Product_Type  
                      AND A.Price>Price  
              )  
          AND Quantity<=0  
    ORDER BY   
        Product_ID,  
        Product_Type  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S103        P101       T101         15          5  
    S102        P101       T102         15          2  
    S102        P101       T103         20          3  
     
    (3 行受影响)  
     
     
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Tuesday, January 13, 2009 9:15 AM
  •  but one condition is remaining...

    Supplier_Id    Product_ID  Product_Type   Price      Quantity
    S101                P101              T101                  15                0
    S102                P101              T101                  15                 0
    S103                P101              T101                  15                0
    S102                P101              T102                  15               2
    S103                P101              T103                  18               0
    S102                P101              T103                  20               3  


    I update the coloured(       )Portion ..

    In this case, i get result like this
    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S101                P101              T101                  15                 0
    S102                P101              T101                  15                 0 --------------->---(T101 is coming duplicate, when price comes same)
    S103                P101              T101                  15                 0
    S102                P101              T102                  15                 2
    S103                P101              T103                  20                 3


    But I need like this...

    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S101                P101              T101                  15                0     from any supplier (S101 or S102 or S103 
    S102                P101              T102                  15               2
    S103                P101              T103                  20              3


    Alex
    Tuesday, January 13, 2009 9:21 AM
  •  
    ---------------------------------  
    --  Author: liangCK 小梁  
    ---------------------------------  
       
    --> 生成测试数据: @Products  
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)  
    INSERT INTO @Products  
    SELECT 'S101','P101','T101',15,0 UNION ALL 
    SELECT 'S102','P101','T101',15,0 UNION ALL 
    SELECT 'S103','P101','T101',15,0 UNION ALL 
    SELECT 'S102','P101','T102',15,2 UNION ALL 
    SELECT 'S103','P101','T103',18,0 UNION ALL 
    SELECT 'S102','P101','T103',20,3  
     
    --SQL查询如下:  
     
    ;WITH Liang AS 
    (  
        SELECT *  
        FROM (  
            SELECT   
                *,  
                RID=ROW_NUMBER() OVER(PARTITION BY Product_ID,Product_Type ORDER BY Price)  
            FROM @Products  
            WHERE Quantity>=1  
        ) AS T  
        WHERE RID=1  
    )  
    SELECT   
        Supplier_Id,  
        Product_ID,  
        Product_Type,  
        Price,  
        Quantity  
    FROM Liang  
    UNION ALL 
    SELECT   
        Supplier_Id,  
        Product_ID,  
        Product_Type,  
        Price,  
        Quantity  
    FROM (  
        SELECT   
            *,  
            RID=ROW_NUMBER() OVER(PARTITION BY Product_ID,Product_Type ORDER BY Price)  
        FROM @Products AS A  
        WHERE NOT EXISTS(     
                  SELECT *     
                  FROM Liang     
                  WHERE A.Product_ID=Product_ID     
                      AND A.Product_Type=Product_Type     
              )  
              AND Quantity<=1  
    AS B  
    WHERE RID=1  
    ORDER BY   
        Product_ID,  
        Product_Type  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S101        P101       T101         15          0  
    S102        P101       T102         15          2  
    S102        P101       T103         20          3  
     
    (3 行受影响)  
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Tuesday, January 13, 2009 9:36 AM
  •  yes ,its working good , but other one condition comes to fail...

    Supplier_Id    Product_ID  Product_Type   Price      Quantity
    S101                P101              T101                  15                5
    S102                P101              T101                  15                 2
    S103                P101              T101                  15                3
    S102                P101              T102                  15               2
    S103                P101              T103                  18               0
    S102                P101              T103                  20               3  


    I update the coloured(       )Portion ..

    In this case, i get result like this
    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S101                P101              T101                  15                 5
    S102                P101              T102                  15                 2
    S103                P101              T103                  20                 3


    But I need like this...

    Supplier_Id    Product_ID  Product_Type   Price    No_of_Quantity
    S102                P101              T101                  15                2     min quanity from quantity search from user
    S102                P101              T102                  15               2
    S103                P101              T103                  20              3

    example :user  search for quantity '1' > take less quantity with greater than or equal to quantity search by user


    Alex
    Tuesday, January 13, 2009 9:48 AM
  • ---------------------------------     
    --  Author: liangCK 小梁     
    ---------------------------------     
          
    --> 生成测试数据: @Products     
    DECLARE @Products TABLE (Supplier_Id VARCHAR(4),Product_ID VARCHAR(4),Product_Type VARCHAR(4),Price INT,Quantity INT)     
    INSERT INTO @Products     
    SELECT 'S101','P101','T101',15,5 UNION ALL    
    SELECT 'S102','P101','T101',15,2 UNION ALL    
    SELECT 'S103','P101','T101',15,3 UNION ALL    
    SELECT 'S102','P101','T102',15,2 UNION ALL    
    SELECT 'S103','P101','T103',18,0 UNION ALL    
    SELECT 'S102','P101','T103',20,3     
        
    --SQL查询如下:     
     
     
    ;WITH Liang AS 
    (  
        SELECT *  
        FROM (  
            SELECT   
                *,  
                RID=ROW_NUMBER() OVER(PARTITION BY Product_ID,Product_Type ORDER BY Price,Quantity)  
            FROM @Products  
            WHERE Quantity>=1  
        ) AS T  
        WHERE RID=1  
    )  
    SELECT   
        Supplier_Id,  
        Product_ID,  
        Product_Type,  
        Price,  
        Quantity  
    FROM Liang  
    UNION ALL 
    SELECT   
        Supplier_Id,  
        Product_ID,  
        Product_Type,  
        Price,  
        Quantity  
    FROM (  
        SELECT   
            *,  
            RID=ROW_NUMBER() OVER(PARTITION BY Product_ID,Product_Type ORDER BY Price)  
        FROM @Products AS A  
        WHERE NOT EXISTS(     
                  SELECT *     
                  FROM Liang     
                  WHERE A.Product_ID=Product_ID     
                      AND A.Product_Type=Product_Type     
              )  
              AND Quantity<=1  
    AS B  
    WHERE RID=1  
    ORDER BY   
        Product_ID,  
        Product_Type  
     
    /*  
    Supplier_Id Product_ID Product_Type Price       Quantity  
    ----------- ---------- ------------ ----------- -----------  
    S102        P101       T101         15          2  
    S102        P101       T102         15          2  
    S102        P101       T103         20          3  
     
    (3 行受影响)  
    */ 

    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    • Marked as answer by Alex Q8 Tuesday, January 13, 2009 10:15 AM
    Tuesday, January 13, 2009 10:02 AM
  • :)   
    ...
    宝剑锋从磨砺出,梅花香自苦寒来! --- From China
    Tuesday, January 13, 2009 10:03 AM
  •  its working great... thank you very much  :)..... :)
    Tuesday, January 13, 2009 10:15 AM