Posted by Doug on 10/01/37 11:38

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.

> 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.

>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.


>SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);


>(if there is a not null value then show me it and stop searching, the
table is quite big)?

Actually, the above code REQUIRES a complete scan of the table. It
really is pretty bad code. The engine must locate and identify ALL
foo_key's, and figure out which one is lowest with a null.

>Show you the NULL value that does not exist? That makes no sense.
Neither does "stop searching", since SQL is a set-oriented language.
You get the entire result set back; it can be empty or it can have any
number of rows.

Actually, it turns out a set CAN contain one row. You can actually even
REQUIRE that set to contain one row!!!!!

>You need to read a book on RDBMS basics. You have missed the most
important concepts.

celko, you might read a book on fundamental human communications. You
have missed the most important concepts.

> If there is no NULL in klugger, then yuou will get an empty set back.

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.

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!

[Back to original message]