BETWEEN

Question

BETWEEN

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

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

Anipaul SSC-Insane Points: 24681 More actions · Answer 1

why complain folks, question is good, only you have to read it long time to understand.....:)

Marius Els Right there with Babe Points: 763 More actions · Answer 2

I stepped into the answer to quickly as well.... unfortunately selecting yes, as my "interpretation" also (like many others) was running the query on select #1 and #2 multiple times, where my focus should have been on "this a between test" and not "what if the selects are run multiple times".....

good question, if you think a bit longer about it

well better luck next time 😀

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Thanks for the feedback folks... Some good comments. I will try to incorporate them next time.

I did have a bit of a hard time wording it. The goal was to allow you to run the entire script however many times and compare the results from #1 to #2 and not different generations of #1 to each other and #2 to each other. That didn't always come out, so it could have been worded better.

The "consistently" was inserted later to deal with the "1 in a billion" chance that a run of the SELECT...INTO happened to generate a quarter million values all outside of the 3-5 range....which would be the exception where #1 and #2 WOULD in fact be the same. Not likely, but possible.

I do like Philip's rewrite - but it still doesn't quite get to to heart of comparing #1 to #2. You'd have to reword as:

"Would statement #1 always generate a result identical to #2's ?" (answer - no, not always, but once in a blue blue moon, perhaps)

Anyway - as said before - I will take these into account next time..:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mohamed-401375 SSC Eights! Points: 965 More actions · Answer 4

Good question, does a good job of testing knowledge about BETWEEN,

but bad english.

I think the questioner needs to know the difference between 'consistent' and 'same as'.

Consistent means same result everytime and each time

'same as' - statement #1 's result = statement #2 's result

chajere SSC Veteran Points: 292 More actions · Answer 5

I think that this question was correctly formed.:D

GSquared SSC Guru Points: 260824 More actions · Answer 6

Mohamed (6/5/2008)
Good question, does a good job of testing knowledge about BETWEEN,
but bad english.
I think the questioner needs to know the difference between 'consistent' and 'same as'.
Consistent means same result everytime and each time
'same as' - statement #1 's result = statement #2 's result

The word "consistent" is standardly defined as: 1. Having the same make-up, structure or character throughout; 2. Lacking variation; 3. Having agreement

In this context, it works in sense 3. (Yes, I am a dictionary lawyer.)

- 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

SanjayAttray SSChampion Points: 13157 More actions · Answer 7

Good question but bad wording. I said yes and lost a point. After insert no matter how many times you execute these select statements you get same result.

--select statement #1

select count(*) from #fun

where crit between 3 and 5

-- 75356 rows

--select statement #2

select count(*) from #fun

where crit between 5 and 3

-- 0 rows

SQL DBA.

Tom Moreau SSCrazy Points: 2125 More actions · Answer 8

Simon Facer (6/4/2008)
Badly worded question -
Would statements #1 and #2 consistently return the same result set?
How do we define "Same Result" Same result as the other statement? Same result across multiple executions of each statement?
Obviously statement #2 will always return 0 - 5 is greater than 3. DUH. But if you rerun statement #1 (without rerunning the CREATE / INSERT statements), it will always return the same result; just as statement #2 will always return 0.
Sorry, didn't like that question at all. This reminds me of the old style MS certification questions - the answer is open to interpretation of the question.
At least I get a point for this post ...:P

Hear! Hear! This is a very poorly-worded question. I picked up on the BETWEEN nonsense and knew that the second one would always return 0. Also, since the table was not going to be refeshed, the first statement would always return the same result set. So, I answered Yes and was told I was wrong.

C'mon guys. Don't waste our time with such amateurish questions.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I got it right. When I read the question, I interpreted it to mean will the the two queries return the same result set. Obviously, some people read the question one way, and others differently.

This can happen REGARDLESS of how well the question is written. The written word is always open for interpretation.

😎

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

I thought it was a good question, but I must admit to having thought that you could answer Yes if you thought that the question meant that each individual query would always return the same results. But having gotten bitten by misinterpreting BETWEEN before I got it right.

I believe that this is the behavior from SQL 7 forward as before that Between worked as the way you would think in English.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 11

The heart of the question was an illustration of a useful point, so was good. The wording was grammatically correct, and so actually asked exactly what was intended (same result set - singular - not same result sets, so quite explicit about whether result set 1 and result set 2 were identical). However, I will agree that common usage of the English language means we all expect less than perfect explanations, so making it difficult to decide whether to take this question literally or "interpret" it.

In other words, the question was quite correct in its wording, but, for clarity's sake, could have assumed readers to be a little less literate.

Thanks, though, Matt, for the brain-fodder.

Semper in excretia, suus solum profundum variat

Mighty SSCrazy Eights Points: 9063 More actions · Answer 12

I've answered the question correctly, but... Maybe the answer should have been "Can't be determined". Since the rand function is used there might be a very, very, very small chance that the table would not contain any values between 3 and 5, which would consistently give both queries the same result of 0.:D

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 13

Even though I did not let the wording fool me on this one I agree that it is a very poor question. As identified once the fun# table is populated and unitl it is repopulated the result sets returned by each query will not change. As the question is currently worded you can easily argue the correct answer is yes.

-- Mark D Powell --

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

Anirban Paul (6/5/2008)
why complain folks, question is good, only you have to read it long time to understand.....:)

The problem is that designers and PM's write the same kind of mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 15

The question is correctly formed but the correct answer to the question is NOT DETERMINED. or whatever was the third one.

Any school kid knows that the RAND(?) function returns random figure in the range of 0-1 and by multiplying the figure by 10 we would get a random set of numbers between 0 and 10

Again any school kid knows that if the probability of one event is not 100% then the probability of safisfaction of N such events is not 100% as well.

So the probability of the first RAND() NOT falling between 3 and 5 equals 0.8

The probability of the second and the first events NOT falling between 3 and 5 is 0.8*0.8

The probability of 250000 RAND() not falling between 3 and 5 is (0.8)^250000 WHICH IS NOT 0%.

So the result of the COUNT may equal zero with a very slim chance, but the chance EXISTS!!!

ps: instead of RAND they should have used even distribution with a gradient function or something. But RAND is a bad tool to help prove BETWEEN syntax.

What do you think?

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM