Let me show small tricks.
1. Every SQL Select query you write can be turned into a sub-query. All you need to do is to enclose it into brackets and optionally assign an alias. Then you can just select from this sub-query:
SELECT ProductName, Max(CreationSQNO) FROM
(
LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO
FROM CurrentConfiguration1
where [Product Name] like '%Customized%'
) AS a
GROUP BY ProductName;
2. Every query you write can be turned into a CTE - Common Table Expression:
;WITH a AS
(
LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO
FROM CurrentConfiguration1
where [Product Name] like '%Customized%'
)
SELECT ProductName, Max(CreationSQNO)
FROM a
GROUP BY ProductName;
3. You can use MAX() directly in your query:
SELECT
LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
MAX(RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1)) AS MaxCreationSQNO FROM CurrentConfiguration1 where [Product Name] like '%Customized%'
GROUP BY LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name])))
Note: if you need the max value of all Products then remove GROUP BY clause and the column ProductName from the select list, like this:
;WITH a AS
(
LEFT([Product Name], LEN([Product Name]) - CHARINDEX(' ', REVERSE([Product Name]))) AS ProductName,
RIGHT([Product Name], CHARINDEX(' ', REVERSE([Product Name])) - 1) AS CreationSQNO
FROM CurrentConfiguration1
where [Product Name] like '%Customized%'
)
SELECT Max(CreationSQNO)
FROM a;