Posted by Greg D. Moore \(Strider\) on 01/31/06 07:20

"Doug" <drmiller100@hotmail.com> wrote in message
news:1138637442.223978.229570@g43g2000cwa.googlegroups.com...
> Hello,
>
> Good discussions.
>
> >>ordering in an RDBMS, so "first", "next" and "last" are totally
> > >meaningless.
>
>
> >> Really? To me, first, next, and last do have meanings in an RDBMS,
> ><> assuming you have an order.
>
> >Celko can be a bit of a curmudgeon and hates to admit when he's wrong.
But
> here he's right.
>
> >You do not have a defined order in a table.
>
> Hmmmm. I said you CAN define the order that data is retrieved from a
> table. celko says you can't.

No, Joe said you have to use an ORDER BY. And he's correct.

> Obviously everyone knows that the table is
> not stored in any particular order. That is a misperception from old
> timers from the 70's, but most have figured it out by now.

Is it everyone or most? Trust me, I've had programmers swear to me it
doesn't matter. (Over my objections I saw a piece of code go into
production that assumed it would always return ONE row and if it did return
more than one, the "order" would be fine.. Sure enough 4 years later when
it returned the "wrong' row I was able to point out the error to a different
programmer who immediately fixed the problem. You can guess which
programmer is still with the company and which one is long gone..)


> > >You must use an ORDER BY clause on a
> > cursor.
>
>
> > Really? I didn't see that anywhere. I've used cursors all the time
> > without an order by function.
> > my bad.
>
> >Yes it is. You may be able to loop through the cursor set, but each time
> you call that cursor you cannot guarantee the order the results will be
>
> returned in w/o an ORDER by statement. Yes, it may seem that you
> always get
> the same order, but that's just an indirect result of the optimizer and
> can
> change.
>
> hmmm. i use cursors all the time without order. When I do this, I don't
> care about the order. There is nothing that says you HAVE to use a
> cursor with an order.

If you want a reproducible meaning of first, second... last. Or do they
mean different things to you? By saying, "first, last" etc you are
suggesting an order.

> Come to think of it, it is very rare that I use a cursor with an order.
> I sometimes order the results though.

>
>
> >What defines "first" here though? First in this case is the first one
that
> the optimizer happens to return. That can chang from call to call.
> (of
> course this particular example is pointless snice if fieldname is NULL
> you
> might as well just say select NULL
>
> Read the code again. As i read it, he wanted to know if a key name
> existed where something was null. Yes, you can use "exists", but that
> can be REALLY slow.
> Using "first" with or without an order is a valid method of checking
> for the existance fo something. It is an efficient method, and it
> solves business problems.

The problem *I* have (and I suspect Joe does to) is the use of the word
"first". It's an ordinal. It assumes an "order"

It shows an implicit mindset.

Now, reading your reply here, it appears to me what you're really saying is
"return ANY row that has this as null" which is different. If you
understand the difference (and I'm guessing you do) I'll shut up. :-)

>
> I believe celko is an arrogant, pompous individual who is not nearly as
> smart as he thinks he is. if he were somewhat smarter, he would be able
> to help people instead of belittling and confusing them.

Well, if folks can get past his pompous attitude, there is value there. He
does have a point which he doesn't express well. If you approach SQL with
the wrong mindset, you end up with horrible code. I've seen programmers who
are great programmers who come to SQL and try to write procedural code,
looping over results using cursors, etc. when a set based alternative is
clearly a better solution. But they can't see that.

Now.. on his table and fieldnaming thing... well... I think he does go a bit
into the deepend on that. :-) (but again, just from responses I've seen
here I can see why things like tblFOO and vwBAR bother him. :-)

>
> a goal on these groups is to help and get help. i believe he
> intereferes with that.
>

[Back to original message]