Can u explain me Find max value sql query as i mentioned below

Question

0.00/5 (No votes)

See more:

SQL

Declare @MaxValue int
Select @MaxValue=(case when @MaxValue>marks then @MaxValue else marks end) From [stud].[dbo].[Stud]
Select @MaxValue

How this query will work?
can you help me step by step.

I don't know how this will execute step by step so that i can know its flow of execution.

refer below table

VB

Posted 10-Dec-13 0:36am

patil.nitin8358

Updated 10-Dec-13 0:43am

v3

Add a Solution

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)

OriginalGriff · Accepted Answer · 2013-12-10T00:39:00

Solution 1

Or you could just do this:

SQL

SELECT MAX(marks) FROM MyTable

Posted 10-Dec-13 0:39am

OriginalGriff

Comments

patil.nitin8358 10-Dec-13 6:45am

Hello OriginalGriff,

Thanks for that, i dont know the query execution step by step

could u please help me.

OriginalGriff 10-Dec-13 6:54am

What part of it do you not understand? You wrote it!

patil.nitin8358 10-Dec-13 6:59am

Select @MaxValue=(case when @MaxValue>marks then @MaxValue else marks end) From [stud].[dbo].[Stud]

As it returning @MaxValue=300

how will it compare @MaxValue & marks then finally returning @MaxValue=300

OriginalGriff 10-Dec-13 7:08am

SELECT operates on all rows in the specified table, so each "marks" column value is compared in turn against the current value of @MaxValue, and @MaxValue is updated in turn.

patil.nitin8358 10-Dec-13 7:31am

suppose

first row marks=100
@MaxValue>marks i.e. 0 >100 it return false
so @Maxvalue is initialize to 100 so @Maxvalue=100

second row marks=200
@MaxValue>marks i.e. 100 > 200 it return false
so @Maxvalue is initialize to 200 so @Maxvalue=200

Third row marks=300
@MaxValue>marks i.e. 200 > 300 it return false
so @Maxvalue is initialize to 300 so @Maxvalue=300

Fourth row marks=50
@MaxValue>marks i.e. 300 > 50 it return true
so @Maxvalue is initialize to 300 so @Maxvalue=300

Finally it select @Maxvalue which contain 300

Please confirm . Is it correct?

Thanks...

Regards
Nitin

OriginalGriff 10-Dec-13 8:37am

Yes. that's about it.
The only complication is that the order in which the rows are compared may not be exactly what you expect - without an ORDER BY clause on your SELECT...FROM SQL is at liberty to process rows in any order it sees fit, which may or may not be in the order they were inserted. In this case it doesn't matter, but if you use this technique for operations which are order dependant, it could become significant.