How to find max value in a alias column in sql server 2008?

Question

1.00/5 (1 vote)

See more:

Hello,
How to find max value in a alias column in sql server 2008?

My query is:-

SQL

SELECT
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%'

My Result is:-

ProductName                        CreationSQNO
Customized Kreation Wardrobe       1
Customized Kreation Wardrobe       2
Customized Kreation Wardrobe       6
Customized Kreation Wardrobe       4
Customized Kreation Wardrobe       n

Now i want to find highest or max value in CreationSQNO column.
So,How it can be possible?

Please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

Posted 20-Mar-14 16:35pm

ankit.agg1984

Updated 20-Mar-14 17:10pm

chaau

v2

Add a Solution

Comments

Peter Leow 20-Mar-14 23:16pm

You have taken my solution from http://www.codeproject.com/Answers/747186/How-can-we-Split-or-Separate-string-and-integer-va?cmt=604482#answer2
Have you forgotten to accept that as solution?

Peter Leow 20-Mar-14 23:42pm

I have noticed that you have hardly accepted any solutions for all your 141 questions to date.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Answer 1 · 2014-03-20T17:22:00

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:

SQL

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:

SQL

;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:

SQL

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:

SQL

;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;