Returning Rows Containing the Highest Product IDs

In the table and sample data that Listing 3 shows, I constructed product IDs from three different source values in an SSS-WWW-PPP format. Digits 1-3 (SSS) represent the supplier ID, digits 5-7 (WWW) represent a warehouse ID, and digits 9-11 (PPP) represent the original product ID within the supplier's warehouse. I need to write a T-SQL query that returns the highest product ID for each supplier-plus-warehouse combination. Using the sample data in Listing 3, the query should return three product IDs: 123-001-003, 123-002-002, and 234-001-002. If the supplier ID, warehouse ID, and product ID were separated into three columns, I would use the following query:

SELECT supplierid, warehouseid, MAX(productid) AS mx_productidFROM ProductsGROUP BY supplierid, warehouseid

However, I'm not sure how to provide a solution when all three values are concatenated into one value. Can I use T-SQL to return those three rows, or do I have to loop through the entire recordset and weed out the lower sequences programmatically?

You can use T-SQL to return the rows you want by including an expression involving the productid column in the GROUP BY clause. The trick is to define the group as the seven leftmost characters in productid, which include only the supplier ID and warehouse ID. Extracting only the supplier-plus-warehouse information and using it in the GROUP BY clause is very similar to specifying separate supplier ID and warehouse ID columns when all three values are separated into different columns. The following query retrieves the maximum product ID for each supplier-plus-warehouse combination, returning the data you seek:

SELECT MAX(productid) AS mx_rlcFROM ProductsGROUP BY LEFT(productid, 7)

Note that the ANSI SQL-92 standard doesn't let you use an expression in the GROUP BY clause; you can use only column names. Using the expression in the GROUP BY clause is a T-SQL extension to the ANSI SQL-92 standard.