February 5, 2011 at 2:08 pm
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
February 5, 2011 at 2:49 pm
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?
February 5, 2011 at 3:15 pm
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
February 6, 2011 at 9:16 am
Thanks for the solution,
February 6, 2011 at 9:28 am
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.
February 6, 2011 at 12:12 pm
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?
February 6, 2011 at 2:43 pm
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
---------------------------------------------------------------------
February 6, 2011 at 3:56 pm
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
February 6, 2011 at 3:57 pm
No he didn't but this question beats me too
February 6, 2011 at 4:10 pm
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);February 6, 2011 at 4:15 pm
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... 😉
February 7, 2011 at 7:16 am
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
February 7, 2011 at 9:43 am
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.
February 8, 2011 at 2:08 am
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
February 8, 2011 at 1:56 pm
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.
Viewing 15 posts - 1 through 15 (of 76 total)
You must be logged in to reply to this topic. Login to reply