Re: How to find first not null value in column


"Doug" <drmiller100@xxxxxxxxxxx> wrote in message
news:1138582937.168569.155440@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> celko writes:
>
> >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.

>
> > If you want an ordering, then you need to havs a column
> that defines that ordering.
>
> Well, you could use a set of columns, function, or a join into another
> table to use a column from another table.
>
> >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.


>
> >Next, you are talking about SQL as if you were in a file system, where
> you read one record at a time and have explicit control flow via
> procedural statements. That is also totally wrong; SQL is a
> declarative, compiled language.
>
> Hmmmmm. You can read one record at a time and have explicit control
> flow via procedural statements. That IS included in the system. Most of
> us consider the concept of "SQL" to include a paradigm for a relational
> database. What does this mean? In my paradigm, you get to use tables.
> In your's, you just get to write language, compile it, but never run
> it.

Here is where Celko is getting onto his high horse. Strictly speaking in
regards to SQL-92 and SQL-99 he's pretty much right. Speaking for T-SQL
he's incorrect. He prefers to think everything should match SQL-92/99.

> Well, you could write code to tell the engine to return exactly one row
> back, whether it is null or not.
>
> select top 1 fieldname from filename where fieldname is null
>
> returns the "first" null if there is one, and an empty set if not.

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.


>
> Perhaps though I am missing your point celko. If so, could you be more
> precise? SQL really lends itself to precise examples, and broad inexact
> generalities often confuse the issues.
> Thanks, and have a good day!
>


.

Relevant Pages

  • Re: How to find first not null value in column
    ... meaningless. ... To me, first, next, and last do have meanings in an RDBMS, ... SQL is a ... celko, you might read a book on fundamental human communications. ...
    (comp.databases.ms-sqlserver)
  • Re: Examples of SQL anomalies?
    ... then the answer is clearly zero. ... ie. SQL can count zero rows with count but not with sum. ... Fabian Pascal would say that he is doing a Celko. ... bringing up intricate arbitrary logics like Johnston's is phony in this ...
    (comp.databases.theory)
  • Re: How to find first not null value in column
    ... I think celko has missed the 'very ... SQL Server MVP ... >>You need to read a book on RDBMS basics. ... >> If there is no NULL in klugger, then yuou will get an empty set back. ...
    (comp.databases.ms-sqlserver)
  • Re: How to find first not null value in column
    ... To me, first, next, and last do have meanings in an RDBMS, ... >>Celko can be a bit of a curmudgeon and hates to admit when he's wrong. ... I've had programmers swear to me it ... You may be able to loop through the cursor set, ...
    (comp.databases.ms-sqlserver)
  • Re: How to find first not null value in column
    ... >Celko can be a bit of a curmudgeon and hates to admit when he's wrong. ... You may be able to loop through the cursor set, ... the optimizer happens to return. ... Using "first" with or without an order is a valid method of checking ...
    (comp.databases.ms-sqlserver)