February 8, 2011 at 2:04 pm
nadabadan (2/8/2011)
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.
... or that I've been working with T-SQL for 10 years, performed rediculously difficult algorithms, but really never saw the reason to make my life harder on myself by ignoring my screwdriver to find new ways to use my hammer and wrench.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 8, 2011 at 2:07 pm
nadabadan (2/8/2011)
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.
If that's the answer to my question about oddball solutions, then I'll point them to this site, where they can see dozens of my posts that include T-SQL solutions I've come up with.
- 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 8, 2011 at 3:04 pm
Craig Farrell (2/8/2011)
nadabadan (2/8/2011)
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.... or that I've been working with T-SQL for 10 years, performed rediculously difficult algorithms, but really never saw the reason to make my life harder on myself by ignoring my screwdriver to find new ways to use my hammer and wrench.
Right and if you have 10 years of experience with T-SQL, this question would have been a breeze and you would understand the REASON for the question. And since you bring up "algorithms", this CASE question is a quiz about an algorithm.
February 8, 2011 at 3:12 pm
nadabadan (2/8/2011)
Right and if you have 10 years of experience with T-SQL, this question would have been a breeze and you would understand the REASON for the question. And since you bring up "algorithms", this CASE question is a quiz about an algorithm.
Not entirely sure why the interviewer was stuck on the CASE component anyway. A simple order by DESC with a top 1 descriptor deals with it, cross apply for multiple rows. It's still a silly question to me. To drive a screw, grab a sledgehammer.
Also, to make sure that we're debating apples to apples:
Algorithm:
A step-by-step problem-solving procedure, especially an established, recursive computational procedure for solving a problem in a finite number of steps.
It's not any one specific component.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 8, 2011 at 3:23 pm
GSquared (2/8/2011)
nadabadan (2/8/2011)
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.
If that's the answer to my question about oddball solutions, then I'll point them to this site, where they can see dozens of my posts that include T-SQL solutions I've come up with.
And no one would be impressed. Take a deep breath and think about the question and WHAT the question is trying to get at. Do you really believe anyone would implement a CASE method over a MAX function? Your 3 possibilities are a joke. Did you throw a hissy fit when your math teacher asked you to put away your calculator for a test on long division. Someone answering "SELECT MAX( columnid ) FROM table" reveals nothing to interviewer. But having someone implement an ALGORITHM to find the maximum value tells me that the interviewee has a good grasp of T-SQL, the underlying logic and implementation and has an ability to think.
February 8, 2011 at 3:28 pm
Craig Farrell (2/8/2011)
nadabadan (2/8/2011)
Right and if you have 10 years of experience with T-SQL, this question would have been a breeze and you would understand the REASON for the question. And since you bring up "algorithms", this CASE question is a quiz about an algorithm.Not entirely sure why the interviewer was stuck on the CASE component anyway. A simple order by DESC with a top 1 descriptor deals with it, cross apply for multiple rows. It's still a silly question to me. To drive a screw, grab a sledgehammer.
Also, to make sure that we're debating apples to apples:
Algorithm:
A step-by-step problem-solving procedure, especially an established, recursive computational procedure for solving a problem in a finite number of steps.
It's not any one specific component.
The interviewer wasn't asking what is the best or even a good way to find the min/max value. When you are forced to use a CASE statement, you have to implement a basic algorithm for finding the maximum or minimum values of a set/list. If you have a computer science background, I think you will have an appreciation for this question. Any basic data structures and algorithms book would have something like this.
February 8, 2011 at 3:48 pm
nadabadan (2/8/2011)
The interviewer wasn't asking what is the best or even a good way to find the min/max value. When you are forced to use a CASE statement, you have to implement a basic algorithm for finding the maximum or minimum values of a set/list. If you have a computer science background, I think you will have an appreciation for this question. Any basic data structures and algorithms book would have something like this.
Not entirely sure why you're up in arms over this one, nadabadan. It might just be the way I'm reading your writing style. Either way:
A CASE statement is a decision tree with immediate exit on true, like an ifelse tree. In no way does this single row (note, single row) decision tree affect the changes across multiple rows. Since Case itself does not do this, you would need to reinvent the wheel to perform multiple row level work using a single row operator.
There are a number of ways you COULD use case here, such as the one Magoo pointed out which uses the serial updating/selecting model by going against the declaritive model of the optimizer to the actual row-level operations that occur under the hood and are exposed by things like 3 way value setting, which can drive the 'quirky update'. It's considered bad practice unless you have a darn good reason to do it, too.
Another way of using CASE would be internal to a cursor loop, performing an equivalent statement to Magoo's just outside the multi-row select wrapper, testing the value of the fetch. Again, poor practice, you've forced a manually built loop.
A third way to use the CASE for this would be part of a while loop, using a counter to go against each ID. Even worse than the cursor as you've avoided any filtering, once again reinventing the wheel, and you could once again CASE the statement to a secondary variable.
In all of these cases, you have not only avoided best practice, you've avoided reasonable coding standards, by trying to use a single row operator to determine a multiple row equation. I stand by my statement. This is a silly question, if you force the CASE statement issue. Due to my background, I specifically have NO appreciation for this question, and would immediately be concerned about the experience of the interviewer and if he had downloaded the question off the net.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 8, 2011 at 4:08 pm
Not entirely sure why you're up in arms over this one, nadabadan. It might just be the way I'm reading your writing style. Either way:
The only reason I'm up-in-arms is because others( you and GSquared mainly ) are up-in-arms about a simple interview question. To me, this interview question is a simple benign question with some merits. Using the CASE statement instead of MAX would not be best practices. That much is OBVIOUS to me and I'm sure it was OBVIOUS to the interviewer. So why ask this question? To me, it shows an interviewer how much an interviewee know about T-SQL, how much the interviewer knows about basic query execution, how much the interviewer knows about basic algorithm and whether or not the interviewer is able to think. Now if the interview question was "Our company believes the CASE statement is the best way to find the max column value of a table, so show us how you would use the CASE statement to find the max value.", then I'd agree with your "outrage". But that's not what the question is, is it.
February 8, 2011 at 4:32 pm
nadabadan (2/8/2011)
The only reason I'm up-in-arms is because others( you and GSquared mainly ) are up-in-arms about a simple interview question. To me, this interview question is a simple benign question with some merits.
I guess it's perception then, having been through many interviews. A question like this is usually loaded. In the first case it's the technical interviewer trying to show off. In the second it indicates a 'Job for Bob'. Usually some strange, off the wall questions that really are non-standard except in extreme exceptions, presented as standard case, that you can't answer easily so they can prove 'no candidate but Bob can do the job'.
If neither of these are the case, then it's probable that the interviewer is looking for one of two answers. A real answer he got off the internet because he found some neat 'trick' and can use it as a technical question, or the candidate responding: "Why, are your customers complaining that queries are running too quickly?"
So why ask this question?
My issue exactly.
To me, it shows an interviewer how much an interviewee know about T-SQL, how much the interviewer knows about basic query execution, how much the interviewer knows about basic algorithm and whether or not the interviewer is able to think.
If you want to show thought, there's many other more accurate approaches that doesn't test your ability to do things the 'wrong way'.
Now if the interview question was "Our company believes the CASE statement is the best way to find the max column value of a table, so show us how you would use the CASE statement to find the max value.", then I'd agree with your "outrage". But that's not what the question is, is it.
The question is poor regardless of the desire to use it in the context of SQL Server. You've gone off on computer science theory backgrounds (what A* pathfinding, Bubble tree logic, and hardware buffer usage has to do with coding in SQL Server is beyond me...) and algorithmic methodology after I simply mentioned the word. Yet, you have yet to mention what it shows, in SQL Server, other than the ability to show you can puzzle out bad ways to do things. What specific items does this question highlight that you can't arrive at without using poor practices?
To quote you directly:
Take a deep breath and think about the question and WHAT the question is trying to get at.
Try this yourself. It's getting at one thing: Can you figure out how to code in SQL badly?
If you want to test an interviewee in design, the way they think, and their proficiency in T-SQL, there are other, simpler ways, even using the same topic. For example: Describe to me three ways to find the most recent row from a single table with a datetime field, and describe to me the methods you'd use to determine which was fastest for your specific case.
Similar question (requires a MAX on the datefield), yet derives much more information and you avoid completely poor coding requirements that don't make sense to anyone who regularly works with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 8, 2011 at 5:38 pm
I guess it's perception then, having been through many interviews. A question like this is usually loaded.
I didn't find the question loaded, an attempt by the interviewer to show off or an example of a neat trick. Rather it was simple, straightforward and fairly obvious.
If you want to show thought, there's many other more accurate approaches that doesn't test your ability to do things the 'wrong way'.
What nonsense. The question doesn't test your ability to do things the 'wrong way', but rather tests whether you can solve a simple problem.
The question is poor regardless of the desire to use it in the context of SQL Server. You've gone off on computer science theory backgrounds (what A* pathfinding, Bubble tree logic, and hardware buffer usage has to do with coding in SQL Server is beyond me...) and algorithmic methodology after I simply mentioned the word. Yet, you have yet to mention what it shows, in SQL Server, other than the ability to show you can puzzle out bad ways to do things. What specific items does this question highlight that you can't arrive at without using poor practices?
Computer science theory background? What the hell is bubble tree logic? Hardware buffer usage? You are just writing nonsense to sound intelligent. The algorithm I was referring to is the simple algorithm every computer science student has to solve. Given a set, finding the max or min value.
Try this yourself. It's getting at one thing: Can you figure out how to code in SQL badly?
Wrong. You can tell a lot about what someone knows by how and whether someone can answer this question. You can tell NOTHING by having someone write "SELECT MAX( column ) FROM table".
Similar question (requires a MAX on the datefield), ...
You just don't get it. The point was not to use MAX. The point was to implement the MAX function in a way. By answering the question using CASE, you describe how the MAX function may be implement behind the scenes.
February 8, 2011 at 5:48 pm
nadabadan (2/8/2011)
The interviewer wasn't asking what is the best or even a good way to find the min/max value. When you are forced to use a CASE statement, you have to implement a basic algorithm for finding the maximum or minimum values of a set/list. If you have a computer science background, I think you will have an appreciation for this question. Any basic data structures and algorithms book would have something like this.
Are you the same person as nzngh who asked the original question that started this topic? If not, how do you claim to know what the precise wording of the question was, or what the interviewser's intent in asking it was? Your view requires a very specific wording.
I'm an experienced interviewer, and like many others I ask people how they would go about doing something stupid hoping to get some version of "I wouldn't do that" as an answer (if someone tells me how he would do that he is in my view unemployable, so I've wasted my time and the company's money on a pointless interview); unless you were actually present at that interview I'm pretty sure that all of Grant, Craig, and myself have a far better idea of the interviewer's intent than you have.
The algorithm posted by Mr Magoo is one way of using a case statement as part of the solution; it will likely perform worse than using MAX, but better than using ORDER BY with TOP 1; if the optimiser is really good (dubitable) it shouldn't perform much worse than using MAX will, and should perform a lot better than using ORDER BY will (unless the table has an index whose first key column is the column whose maximum you are looking for, in which case TOP 1 with ORDER BY is the appropriate method if performance is more important than clarity). It is however an appallingly opaque way of doing something that can be done in a very transparent manner. People who write needlessly opaque code are a menace in any development team.
Tom
February 8, 2011 at 5:56 pm
Tom.Thomson (2/8/2011)
The algorithm posted by Mr Magoo ... (edited for brevity) ... is however an appallingly opaque way of doing something that can be done in a very transparent manner. People who write needlessly opaque code are a menace in any development team.
Just to clarify - I wrote that code just to show the OP one way that it could be done because the OP said it was puzzling him - not as a recommendation of good code 😀
Also, I like being a menace in a team 😉
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 8, 2011 at 6:08 pm
Are you the same person as nzngh who asked the original question that started this topic? If not, how do you claim to know what the precise wording of the question was, or what the interviewser's intent in asking it was? Your view requires a very specific wording.
I am not and neither are you, GSquared or Craig. It was GSquared's 3 "possibilities" that made me post in the first place. So maybe we ALL should stop making stupid assumptions then.
I'm an experienced interviewer, and like many others I ask people how they would go about doing something stupid hoping to get some version of "I wouldn't do that" as an answer (if someone tells me how he would do that he is in my view unemployable, so I've wasted my time and the company's money on a pointless interview);
The difference between me and you is that I understand WHY this question was asked. You do not. Simple as that.
unless you were actually present at that interview I'm pretty sure that all of Grant, Craig, and myself have a far better idea of the interviewer's intent than you have.
Quite the assumption you have there.
The algorithm posted by Mr Magoo is one way of using a case statement as part of the solution;
Yes it is and it happens to be the one that I came up with also.
It is however an appallingly opaque way of doing something that can be done in a very transparent manner. People who write needlessly opaque code are a menace in any development team.
No kidding.
February 8, 2011 at 6:49 pm
mister.magoo (2/8/2011)
Tom.Thomson (2/8/2011)
The algorithm posted by Mr Magoo ... (edited for brevity) ... is however an appallingly opaque way of doing something that can be done in a very transparent manner. People who write needlessly opaque code are a menace in any development team.Just to clarify - I wrote that code just to show the OP one way that it could be done because the OP said it was puzzling him - not as a recommendation of good code 😀
yes, I think we all knew that!
Also, I like being a menace in a team 😉
Me too!:hehe:
Tom
February 8, 2011 at 7:11 pm
nadabadan (2/8/2011)
The algorithm I was referring to is the simple algorithm every computer science student has to solve. Given a set, finding the max or min value.
The algorithm which computer science students should be learning (to get the max of a set) is one of
MaxOfSet S U {x} = Reduce lambda x y : [ x > y ? x ; y ] S x
or perhaps (in a language where Reduce isn't a built in function, if the language is used in an environment where no-one has added a standard Reduce function to the run time environment)
Max2(x,y) = [ x > y ? x ; y]
MaxOfSet({x}) = x
MaxOfSet (S U {x}) = Max2(MaxOfSet(S),x)
or even
Max {x} x
Max {x,y} y <= x = y
Max {x,y) x <= x > y
Max S U {x} z <= Max S z AND Max {x,z} z
Max S U {x} x <= Max S z AND max {x,z} z
or perhaps a process description in CCS or CSP instead or something derived from one of those two languages or another process calculus, or a description in a formal specification language like Z (but not in a hackers' paradise like VDM).
nadabadan (2/8/2011)
The interviewer wasn't asking what is the best or even a good way to find the min/max value. When you are forced to use a CASE statement, you have to implement a basic algorithm for finding the maximum or minimum values of a set/list. If you have a computer science background, I think you will have an appreciation for this question. Any basic data structures and algorithms book would have something like this.
SQL has no CASE statement. Neither does T-SQL.
There are case expressions embedded in the two functional language descriptions of the MAX algorithm given above.. No "case statements", though. Like SQL and T-SQL, some functional programming languages and some process description languages contain case expressions, but none of them has the case statement that you refer to. And I'm not aware of any logic programming language that contains even case expressions, let alone case statements (but it's a couple of decades since I was up with the state of the art in logic programming, so that may have changed). If you think we can teach computer science using languages that have case statements (languages that are essentially non-declarative and procedural with control flow operators) you are very much mistaken (some universities pretend to do that; but what they actually teach is currently fashionable technology, not computer science).
Tom
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply