To get the Max value from one column with case statement without using any aggregate functions

Question

Post reply

To get the Max value from one column with case statement without using any aggregate functions

Marcel Proust

Mr or Mrs. 500

Points: 596
More actions
February 5, 2011 at 2:08 pm

#99542

Hello there;
I need to know is there any way to get the maximum value of one column without using aggregate functions such as max or count. In other word I'm trying to simulate Max function by using case statement.
Say I have a table names "Items" and I have a column names BidPrice. I need to get the max price out of this column without using max function an by use of case statement
Thank you
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could use ROW_NUMBER() OVER(ORDER BY YourColumn DESC) within an CTE and query for Row=1 in an outer SELECT.

Don't think you'd need the CASE statement for it though...

But the question is: WHY?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

This kind of weird requirement is usually schoolwork. Is it this time?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Marcel Proust Mr or Mrs. 500 Points: 596 More actions · Answer 3

Marcel Proust

Mr or Mrs. 500

Points: 596

February 6, 2011 at 9:16 am

#1282878

Thanks for the solution,

Marcel Proust Mr or Mrs. 500 Points: 596 More actions · Answer 4

It was an Interview question, surly I couldn't answer it. I have tried it in any possible way that I know but I got only Null though with two column of min and max it works and give me min and max but having only one column nop.

LutzM SSC Guru Points: 107049 More actions · Answer 5

nzngh (2/6/2011)
It was an Interview question, surly I couldn't answer it. I have tried it in any possible way that I know but I got only Null though with two column of min and max it works and give me min and max but having only one column nop.

So, what was your original reply to this question?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 6

did the interviewer give a solution?

Beats me where the case statement would come into it but this would do it:

select top 1 yourcolumn from yourtable order by yourcolumn desc

---------------------------------------------------------------------

Marcel Proust Mr or Mrs. 500 Points: 596 More actions · Answer 7

I said "thanks for the solution" it works perfect but not exactly in the way I needed to know still matter of being thank full for

Marcel Proust Mr or Mrs. 500 Points: 596 More actions · Answer 8

Marcel Proust

Mr or Mrs. 500

Points: 596

February 6, 2011 at 3:57 pm

#1282906

No he didn't but this question beats me too

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

I suppose if all you wanted was the max price from all the rows in the table or for a selected item, and you HAD to use CASE, you could do this:

DECLARE @MaxPrice DECIMAL(14,2);

SET @MaxPrice = 0.00;

SELECT

@MaxPrice = CASE

WHEN COALESCE(BidPrice,0.00)>@MaxPrice THEN BidPrice

ELSE @MaxPrice

END

FROM items

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

LutzM SSC Guru Points: 107049 More actions · Answer 10

george sibbald (2/6/2011)
did the interviewer give a solution?
Beats me where the case statement would come into it but this would do it:
select top 1 yourcolumn from yourtable order by yourcolumn desc

Ouch. :blush:

I did read more into the question then what was actually asked (I thought of the max BidPrice per item...)

Re-reading the question, your solution would be much better than mine.

Both could be wrapped with a SELECT CASE WHEN 1=1 statement...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 11

nzngh (2/6/2011)
It was an Interview question, surly I couldn't answer it. I have tried it in any possible way that I know but I got only Null though with two column of min and max it works and give me min and max but having only one column nop.

My reply would be, "Why would I use an oddball solution like that when Min and Max are built for that kind of thing? Building code that follows common, standard practices makes it easier to document and maintain. Building code with 'tricks' that nobody knows makes it more difficult on everyone, including me. So why would I do something like that?"

I see three possibilities here:

1. A prior dev/DBA used oddball tricks like that, and everyone currently working there hates it, and it's a "trick question" to see if you'd continue practices that everyone hates.

2. The person doing the interview uses oddball tricks like that, either out of miseducation, ego gratification, or "job security".

3. The company or department has policies that force them to use oddball coding practices, probably due to Pointy-Haired-Boss Syndrome.

If it's either 2 or 3, you don't want to work there anyway. If it's 1, then my reply would get a good reaction.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Marcel Proust Mr or Mrs. 500 Points: 596 More actions · Answer 12

Well he didn't look so much like an odd fish nor uneducated how ever I don't know about their considerations for they didn't explain and I didn't ask.:hehe: You may right but honestly what I am looking for is the answer, if there would be an answer.

mglumpa SSC Rookie Points: 47 More actions · Answer 13

I would think of using order by then, using top 1 to get the max value.

select top 1 given_field from your_table order by given_field desc

using case statement only makes sense if you want to find which one is larger between two columns for a given row

nadabadan SSCommitted Points: 1503 More actions · Answer 14

GSquared (2/7/2011)
nzngh (2/6/2011)
It was an Interview question, surly I couldn't answer it. I have tried it in any possible way that I know but I got only Null though with two column of min and max it works and give me min and max but having only one column nop.
My reply would be, "Why would I use an oddball solution like that when Min and Max are built for that kind of thing? Building code that follows common, standard practices makes it easier to document and maintain. Building code with 'tricks' that nobody knows makes it more difficult on everyone, including me. So why would I do something like that?"
I see three possibilities here:
1. A prior dev/DBA used oddball tricks like that, and everyone currently working there hates it, and it's a "trick question" to see if you'd continue practices that everyone hates.
2. The person doing the interview uses oddball tricks like that, either out of miseducation, ego gratification, or "job security".
3. The company or department has policies that force them to use oddball coding practices, probably due to Pointy-Haired-Boss Syndrome.
If it's either 2 or 3, you don't want to work there anyway. If it's 1, then my reply would get a good reaction.

Or how about if you want to see how much a person REALLY knows about T-SQL or want to see their thought process. I doubt these people actually use the CASE statement to find the MAX value. But forcing someone to write a solution using CASE shows whether that person has some experience in T-SQL or just some superficial knowledge of T-SQL gleaned of a book the night before the interview.

To get the Max value from one column with case statement without using any aggregate functions

Cookies on SQLServerCentral