none
TSQL how can one get a single row of subgroups and the max value of that subgroup?

    Question

  • From the TSQL query shown below I'm trying to get this result and with the 2 lines shown in bold get errors

    Msg 207, Level 16, State 1, Procedure spGetContainerIDParams, Line 57
    Invalid column name 'LotID'.
    Msg 207, Level 16, State 1, Procedure spGetContainerIDParams, Line 57
    Invalid column name 'RevID'

    What can I code to get the desired results shown?

    The desired results is the following and need to get the max revisionID row. The second grouping is what I currently have without the MAX function to get the highest revisonID. The revisionID can be any increasing interger (Not shown are all the columns but only 3 columns that illustrate what results are desired).

    LotID customerProductID revisionID
    37140 CH-PC1053-D1            4
    37939 CH-PC1053-D1            5
    39084 CH-PC1053-D1            2


    LotID customerProductID revisionID
    37140 NULL                         1
    37140 31008245                  2
    37140 CH-PC1053-D1           3
    37140 CH-PC1053-D1           4

    37939 NULL                         1
    37939 31008245                  2
    37939 CH-PC1053-D1           3
    37939 CH-PC1053-D1           4
    37939 CH-PC1053-D1           5

    39084    NULL                      1
    39084 31008245                  2


    Msg 207, Level 16, State 1, Procedure spGetContainerIDParams, Line 57
    Invalid column name 'LotID'.
    Msg 207, Level 16, State 1, Procedure spGetContainerIDParams, Line 57
    Invalid column name 'RevID'


     begin try
      set @barcode_check = dbo.fn_checkDigitMod43(@barcode)
     end try
     begin catch
      set @barcode_check = substring(@barcode, len(@barcode), 1)
      set @barcode = substring(@barcode, 0, len(@barcode))
     end catch

     SELECT
     pl.lotNum as LotID,
     cp.customerProductID,
     **MAX(cp.revisionID) as RevID**
     FROM Containers c
        LEFT JOIN products p ON c.productID = p.productID
        INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
        INNER JOIN Customers cu ON p.customerID = cu.CustomerID
        INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
        INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
        INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
        LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
        LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
        LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
        LEFT JOIN States s ON cs.stateID = s.stateID
     WHERE
         c.barcode = @barcode OR
      c.old_barcode = @barcode OR
      c.old_barcode = @barcode + @barcode_check
     **GROUP BY LotID, RevID**


    • Edited by tonofit Friday, November 15, 2013 10:29 PM
    Friday, November 15, 2013 10:26 PM

Answers

  • Sorry, I was quick in copying:

    ; with lastLot as (
    SELECT
      pl.lotNum as LotID,
      MAX(cp.revisionID) as RevID
      FROM Containers c
         LEFT JOIN products p ON c.productID = p.productID
         INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
         INNER JOIN Customers cu ON p.customerID = cu.CustomerID
         INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
         INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
         INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
         LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
         LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
         LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
         LEFT JOIN States s ON cs.stateID = s.stateID
      WHERE
          c.barcode = @barcode OR 
       c.old_barcode = @barcode OR 
       c.old_barcode = @barcode + @barcode_check
      GROUP BY pl.lotNum 
    ) 
    
    SELECT distinct
      l.LotID,
      cp.customerProductID,
      l.RevID
      FROM lastLot l
     inner join (
    Containers c
         LEFT JOIN products p ON c.productID = p.productID
         INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
         INNER JOIN Customers cu ON p.customerID = cu.CustomerID
         INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
         INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
         INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
         LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
         LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
         LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
         LEFT JOIN States s ON cs.stateID = s.stateID
    ) on l.LotID = pl.lotNum
      and l.RevID = cp.revisionID
      WHERE
          c.barcode = @barcode OR 
       c.old_barcode = @barcode OR 
       c.old_barcode = @barcode + @barcode_check;


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by tonofit Monday, November 18, 2013 6:55 PM
    Monday, November 18, 2013 6:18 PM

All replies

  • ; with lastLot as (
    SELECT
      pl.lotNum as LotID,
      MAX(cp.revisionID) as RevID
      FROM Containers c
         LEFT JOIN products p ON c.productID = p.productID
         INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
         INNER JOIN Customers cu ON p.customerID = cu.CustomerID
         INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
         INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
         INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
         LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
         LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
         LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
         LEFT JOIN States s ON cs.stateID = s.stateID
      WHERE
          c.barcode = @barcode OR 
       c.old_barcode = @barcode OR 
       c.old_barcode = @barcode + @barcode_check
      GROUP BY LotID 
    ) 
    
    SELECT distinct
      l.LotID,
      cp.customerProductID,
      l.RevID
      FROM lastLot l
     inner join (
    Containers c
         LEFT JOIN products p ON c.productID = p.productID
         INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
         INNER JOIN Customers cu ON p.customerID = cu.CustomerID
         INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
         INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
         INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
         LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
         LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
         LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
         LEFT JOIN States s ON cs.stateID = s.stateID
    ) on l.LotID = pl.lotNum
      and l.RevID = cp.revisionID
      WHERE
          c.barcode = @barcode OR 
       c.old_barcode = @barcode OR 
       c.old_barcode = @barcode + @barcode_check;


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Saeid Hasani Saturday, November 16, 2013 4:57 AM
    • Unproposed as answer by tonofit Monday, November 18, 2013 6:02 PM
    Friday, November 15, 2013 10:54 PM
  • Where is the DDL? What you did post is not a correct schema! You have no idea how to do a data model or follow ISO-11179 ruleS. The errors messages are quite clear; you have no data integrity in your schema. 

    A procedure name will never have that silly “sp_” prefix; that is meta data and has meaning in T-SQL dialect. You pass parameters to a procedure; you do not get them from a procedure. The correct name could be “Get_Container_Id” if you used ISO-11179 standards. 

    The old old “fn_” is especially funny. It was required in 1050's FORTRAN II and in 1960's BASIC! A good SQL programmer would never write a UDF (they do not port, they do not optimize) and if they did they would never use that prefix. You are not writing SQL yet. 

    Why do you think everything is an “_id”?? Think about how silly “revision_id” is; shouldn’t it be a “revision_seq”? This scares me and I fear that you have an IDENTITY property on every table (it is not a column!). Now you can model the revisions with CREATE SEQUENCE and let the SQL do all the work! 

    Why do data elements change their names and their attribute properties?? Look at how awful “PL.lotNum AS lot_nbr” iS. An identifier is not the same as a tag number! Also, Google why camelCase does not work. 

    There is no such thing as “cont_type_id” in RDBMS. The attribute can be a “container_id” or a “container_type” but not a hybrid. But worse that, it is not an entity. It is clearly an attribute of a container! I will guess that you meant “container_type” and that the codes are few and static. 

    Likewise, what are the “States”? A political unit or a state of being? Do you know how to write state transition constraints? 

    What is the industry standard encoding for chemicals? I sure hope so! 

    That means an SQL programmer would use 

    CREATE TABLE Containers
    (..
     container_type CHAR(??) NOT NULL
      CHECK (container_type IN (??, ??, ..)),
     container_status CHAR(??) NOT NULL
      CHECK (container_status IN (??, ??, ..)),
    ..);

    Likewise there is no such thing as a generic, universal “barcode” -- look at UPC, EAN, cargo containers, etc. Are you using Bureau International des Containers (ISO-6346 Standards) for the containers? 

    What is bar coded? Why do fail to use any industry standard for your products? I would guess that you are moving to the GTIN (Global Trade Item Number) codes from your vague product_id, but we have no specs and no DDL. 

    Your WHERE clause is a nightmare! You are telling the world that your total lack of any data integrity puts invalid codes into the database and then you try to kludge it in the DML! Would you go to a hospital that uses dirty needles then tried to cure the victor after they are poisoned?  An SQL programmer would have the check digits in the DDL before allowing data into the table. 

    Also, it is rare that you will have more than 1 or 2 OUTER JOINs in a query. It is like having brain cancer, both rare and bad :( 

    Please follow Netiquette and post some DDL so we can actually help you instead of doing kludges with too many tables in weird joins.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, November 16, 2013 6:27 PM
  • Russ - solution almost works but gets - Invalid column name 'LotID' in GROUP BY LotID  error.  This must be - Column alias in a GROUP BY, aliases are for display, unless when the alias is in a subquery, in this case , it becomes the column name.  So what should it be?


    • Edited by tonofit Monday, November 18, 2013 6:16 PM Not correct
    Monday, November 18, 2013 6:14 PM
  • Sorry, I was quick in copying:

    ; with lastLot as (
    SELECT
      pl.lotNum as LotID,
      MAX(cp.revisionID) as RevID
      FROM Containers c
         LEFT JOIN products p ON c.productID = p.productID
         INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
         INNER JOIN Customers cu ON p.customerID = cu.CustomerID
         INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
         INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
         INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
         LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
         LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
         LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
         LEFT JOIN States s ON cs.stateID = s.stateID
      WHERE
          c.barcode = @barcode OR 
       c.old_barcode = @barcode OR 
       c.old_barcode = @barcode + @barcode_check
      GROUP BY pl.lotNum 
    ) 
    
    SELECT distinct
      l.LotID,
      cp.customerProductID,
      l.RevID
      FROM lastLot l
     inner join (
    Containers c
         LEFT JOIN products p ON c.productID = p.productID
         INNER JOIN Chemicals ch ON p.chemicalID = ch.ChemicalID
         INNER JOIN Customers cu ON p.customerID = cu.CustomerID
         INNER JOIN ContainerTypes ct ON p.contTypeID = ct.contTypeID
         INNER JOIN ContainerExpiration ce ON p.chemicalID = ce.chemicalID AND p.contTypeID = ce.contTypeID
         INNER JOIN CustomerSpecifications cp ON p.productID = cp.productID
         LEFT JOIN ContainerStates cs ON c.barcode = cs.containerID
         LEFT JOIN PalletBarcodes pb ON pb.barcode = c.barcode
         LEFT JOIN PalletList pl ON pb.palletID = pl.palletID
         LEFT JOIN States s ON cs.stateID = s.stateID
    ) on l.LotID = pl.lotNum
      and l.RevID = cp.revisionID
      WHERE
          c.barcode = @barcode OR 
       c.old_barcode = @barcode OR 
       c.old_barcode = @barcode + @barcode_check;


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by tonofit Monday, November 18, 2013 6:55 PM
    Monday, November 18, 2013 6:18 PM