Using Max()

Question

Using Max()

Viewing 15 posts - 16 through 30 (of 43 total)

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

sqlnaive SSCoach Points: 17437 More actions · Answer 1

Good QOTD for the people who have confusion over this. Though i had came with this specific condition several times so there was never a question of any confusion. 🙂

Easy points for me this time. 😛

Mighty SSCrazy Eights Points: 9063 More actions · Answer 2

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Good QOTD. Quite a few people may learn from it, although I never could understand why people would misinterpret MAX.

It would make a good interview question - if someone doesn't h=get it right, the only position they are suited to is trainee. With 29% (now 165 - and it was 29% when that represented only 97 people, so it looks as if the proportion is pretty stable) of reponses being wrong, my guess is that some of the people who are answering are very junior, maybe students, and it will be useful for them to get this crrected early in their careers.

Tom

r.stebbens 78867 SSC-Addicted Points: 407 More actions · Answer 4

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! 🙂

Developer, DBA, Pre-Sales consultant.

JHamm73 SSC Enthusiast Points: 196 More actions · Answer 5

I looked over it a good half-dozen times wondering where the trick was that I was missing. 🙂 Good back-to-basics question.

waxingsatirical SSC Eights! Points: 934 More actions · Answer 6

Training is on going with my staff, it's why I asked them the question! 🙂

You might want to ask them what the result of this query is:

Select *

from currency

where Date = '20130713'

and Date = '20130714'

Steve Eckhart SSCommitted Points: 1880 More actions · Answer 7

Got it wrong because the wording of the question implied that it was tricky. But, it was actually the obvious answer.

Steve Eckhart

paul s-306273 SSChampion Points: 10730 More actions · Answer 8

The answer was so obvious I ignored it, thinking that was the reason for the post.

So there was no real need to post this other than to cause confusion?

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

Thanks for the question!

However, the drama of saying that it caught out all your staff made me spend way too much time on it. Thinking I was missing something, I even went off and did a test with my own data, only to confirm that I was right in the first 5 seconds. :w00t:

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Tee Time Hall of Fame Points: 3745 More actions · Answer 10

Oops, I choose (in my mind) the correct answer, and clicked with my, evidently uncoordinated hand, the incorrect choice and hit submit.

Regardless of my shortcomings (a.k.a. excuses), good question! Thanks much. 😛

paul s-306273 SSChampion Points: 10730 More actions · Answer 11

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.

.. so the point of the question was what?

"This is so easy, but my staff don't would all get this wrong."

I suggest that QOTD isn't the correct forum for making such a point.

(And what do the poster's staff think of being ridiculed in this way?)

steve.jacobs SSCommitted Points: 1830 More actions · Answer 12

steve.jacobs

SSCommitted

Points: 1830

November 8, 2013 at 8:01 am

#1665130

Nice question and good "back to basics."

Thanks

logitestus SSCrazy Points: 2928 More actions · Answer 13

My failure answer of this question just goes to show: sometimes second guessing yourself is not a good thing. I had the right answer the first time around but I didn't want to get caught be a gotcha. Doh!

Dana Medley SSCertifiable Points: 6764 More actions · Answer 14

Thomas Abraham (11/8/2013)
Thanks for the question!
However, the drama of saying that it caught out all your staff made me spend way too much time on it. Thinking I was missing something, I even went off and did a test with my own data, only to confirm that I was right in the first 5 seconds. :w00t:

+1 I spent quite a few minutes looking this over trying to figure out the trick or the "gotcha" in it...but there wasn't one.:hehe:

Everything is awesome!

christopher.gray Old Hand Points: 372 More actions · Answer 15

A common pattern is when you want to pull the latest Exchange_Rates from the table.

Before the windowed ranking functions, this was not much fun to write.

This T-SQL will give you the latest exchange rate by currency type:

(the example only had one currency type, but in the real world, you usually have several)

SELECT Currency, Exchange_Rate, Date

FROM

(

SELECT Currency, Exchange_Rate, Date

Row_Number OVER (PARTITION BY Currency ORDER BY Date DESC) AS xRank

FROM Currency

) AS TEMP

WHERE xRank = 1