Using Max()

Question

Using Max()

Viewing 14 posts - 31 through 43 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Rich Mechaber SSChampion Points: 11130 More actions · Answer 1

I'm beginning to think that we need some kind of "posting shorthand" for replying to QotD.

My first suggestion: we need a term that means "I thought this looked straightforward, but I know that these questions are usually designed to trip us up, so I spent 15 minutes looking it over/thinking about it/querying/reviewing BoL/performing divination by entrails before deciding I was just going to answer the way I thought it should be answered. I was right, and I was surprised so many others got it wrong."

Maybe IKIWAR (pron. Icky-War, short for "I Knew I WAs Right"? 😉

Rob, maybe this would help your staff to understand their error: add another row to the table with a DATE of 2013-07-13 (i.e., not the MAX(Date)) and an Exchange_Rate of 0.11999 (i.e., the MAX(Exchange_Rate)) and ask them what this query would return:

SELECT Max(Exchange_Rate), Max(Date) FROM Currency

(Note the reversed ordering of the columns in the SELECT statement, which of course doesn't change the results.)

Would they then have expected to see some kind of grouping on Exchange_Rate?

Thanks for the question and for the explanation as to why it was an appropriate question,

Rich

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

r.stebbens 78867 (11/8/2013)
Mighty (11/8/2013)
paul.knibbs (11/8/2013)
I kind of agree with waxingsatirical...it never occurred to me anyone would think this works otherwise, and I spent a good few minutes trying to figure out what unexpected behaviour of MAX() the question could be asking for! Eventually I just gave up and answered what I thought would happen, just to see what the answer was, and was somewhat surprised when that turned out to be correct. Guess I'm too used to QotDs looking for knowledge of odd bugs or weird behaviour!
I agree with you. I was looking for something out of the ordinary. Especially because it was written "...caught out all of my staff...".
Not wanting to judge or so, but maybe it is worthwhile investing some money for an in-house T-SQL training for all of the staff.
It's really a matter of understanding and avoiding an assumption and the staff I have are pretty junior (1-2 years experience) So I wouldn't expect them to get the question right -although when I explained to them they understood it. It's not something that I would commonly consider doing either [ using Max more than once in a query] or in this particular way - it's just an interesting thing that I thought would catch people out 😎
I think some of the questions are that come up can be a bit too much of a stretch for the less experienced members, so why not throw the odd easy one in for good measure?
Training is on going with my staff, it's why I asked them the question! 🙂

That makes sense.

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Richard Warr SSCertifiable Points: 6963 More actions · Answer 3

At the moment over 300 people have got this one wrong. They will have learned something useful from the question (presumably that both MAXs are independent) and, as such it was a perfectly valid one to ask. Similar situations in other programming environments would have given a different answer.

Implying that they are some sort of idiot because the question was "so obvious" adds nothing to this community. People who got it wrong will visit this discussion as well and would feel justifiably insulted by some of the comments above.

craig.schlieve Old Hand Points: 386 More actions · Answer 4

Steve Eckhart (11/8/2013)
Got it wrong because the wording of the question implied that it was tricky. But, it was actually the obvious answer.

I did the same thing.

Revenant SSC-Forever Points: 42467 More actions · Answer 5

I am using GROUP BY daily, so this was a no-brainer. Thanks, Rob!

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

nice question to end the week, my week got extented this time :hehe:

thanks for sharing !!!

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

PChiragS

SSCarpal Tunnel

Points: 4965

November 10, 2013 at 2:53 pm

#1665355

Nice question.

Thanks Rob.

Zagyg SSCrazy Points: 2091 More actions · Answer 8

I really liked this question; for me (and several others) it was so brain-bogglingly easy that it required almost no thought (apart from those who sensed a trap!), and yet recently there have been a few questions where, in the discussions, everyone is saying "easy one, thanks" and "back to basics" etc. and I'm thinking ... No it wasn't, and I got it wrong!

I have colleagues who would pause and falter over this, but have vastly superior expertise when it comes to aspects of the SQL BI Stack.

It goes to show that it all depends on what you're regularly exposed to, where your specialties lie, and perhaps if you can't quite remember the nuances of that stuff you learnt 15 years ago but only need to know now and again.

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

Easy one for me, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Marcia J SSCertifiable Points: 5649 More actions · Answer 10

andy.brown (11/11/2013)

I really liked this question; for me (and several others) it was so brain-bogglingly easy that it required almost no thought (apart from those who sensed a trap!), and yet recently there have been a few questions where, in the discussions, everyone is saying "easy one, thanks" and "back to basics" etc. and I'm thinking ... No it wasn't, and I got it wrong!

...

Been there often (getting an 'easy' one wrong). It was nice to have it reversed this time and be able to get something right that some people found difficult.

Marcia J SSCertifiable Points: 5649 More actions · Answer 11

Richard Warr (11/8/2013)
At the moment over 300 people have got this one wrong. They will have learned something useful from the question (presumably that both MAXs are independent) and, as such it was a perfectly valid one to ask. Similar situations in other programming environments would have given a different answer.
Implying that they are some sort of idiot because the question was "so obvious" adds nothing to this community. People who got it wrong will visit this discussion as well and would feel justifiably insulted by some of the comments above.

Agree. There are people at several different levels trying to learn from this. What's seems easy for one person might be difficult for another.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ken SSCoach Points: 17191 More actions · Answer 13

Ken

SSCoach

Points: 17191

November 12, 2013 at 9:28 am

#1665774

Thanks for the easy one to end the week on!

marlon.seton SSCrazy Points: 2623 More actions · Answer 14

Of all the QotDs, this is the one I felt most confident about answering so it was good that I got it right.