Hi,
How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?

thx,
Martin

*** Sent via Developersdex http://www.developersdex.com ***

Martin R (martin80@go2.pl) writes:
> How to find first not null value in column whitout chacking whole table
> (if there is a not null value then show me it and stop searching, the
> table is quite big)?

SELECT TOP 1 col FROM tbl WHERE col IS NOT NULL

Note that "first" here is not extremly well-defined, as a table by
a defintion is a unorded set of data.

If there is no index that involves col at row, SQL Server is likely
to scan the clustered index from left to right. (But in theory it
could open parallel steams, and give you a row in the middle.)

If there is an index that involves col, SQL Serer is likely to scan
that index. And if there is an index with col as the first column,
SQL Server is likely to seek that index, and you would get the lowest
value of col. If you then change "SELECT col" to "SELECT *", you may
be back on the table scan again.

If you have any additional criteria to define this "first" value, then
you need to add an ORDER BY clause to the query.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>> How to find first not null value in column without chacking whole table <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor.

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.

Let's make a guess, based on nothing you posted, as to what the DDL
looks like:

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY, -- order by him??
klugger INTEGER, -- target column ??
..);

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)?

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.

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


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

On 28 Jan 2006 11:51:33 -0800, --CELKO-- wrote:

>>> How to find first not null value in column without chacking whole table <<
(snip)
>Let's make a guess, based on nothing you posted, as to what the DDL
>looks like:
>
>CREATE TABLE Foobar
>(foo_key INTEGER NOT NULL PRIMARY KEY, -- order by him??
> klugger INTEGER, -- target column ??
> ..);
>
>SELECT foo_key
> FROM Foobar
> WHERE klugger IS NULL
> AND foo_key
> = (SELECT MIN(foo_key) FROM Foobar);

HHi Joe,

I do hope that you actually intended to post

SELECT MIN(foo_key)
FROM Foobar
WHERE klugger IS NULL

--
Hugo Kornelis, SQL Server MVP

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!

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

Missed the 'most important' concepts, I think celko has missed the 'very
basic' concepts.

Don't let the guy get to you, he doesn't want to let go of 80's programming
models; but more importantly he doesn't want the industry to move on in case
we stop (or start) using the standard that the committee he belonged to
worked on.

He only spouts anything meaningful when he talks about logical models, as
soon as he moves out of that area he shows us just how out of his depth he
really is.

I keep saying to him, go get a job as a junior programmer - i think it would
help him a) communicate better and b) better understand the problems and
architectures of todays environments.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Doug" <drmiller100@hotmail.com> wrote in message
news:1138582937.168569.155440@g14g2000cwa.googlegr oups.com...
> 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!
>

"Doug" <drmiller100@hotmail.com> wrote in message
news:1138582937.168569.155440@g14g2000cwa.googlegr oups.com...
> 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!
>

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

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.

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

I meant to put klugger in the outermost SELECT list. Arrgh!

>> To me, first, next, and last do have meanings in an RDBMS,assuming you have an order. <<

Do you also believe in square circles? This is a matter of definition.
I can post Dr. Codd's 12 rules if you have never read them.

>> I've used cursors all the time without an order by function.my bad. <<

That will give you random results, based on the internal state of the
database at the time of the query.

>> Well, you could write code to tell the engine to return exactly one row back, whether it is null or not. <<

I probably can, but that defeats the whole idea of a set-oriented
language. You wanted a set; an empty set is a set. You are still
thinking in records and files.

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

Please, please, I have worked for years on my "abrassive Zen Master"
Newsgorup persona. Everyone that takes a live class from me is
surprised that I go one-on-one for hours or days after the class until
someone understands things. My attitude is that you should not use a
Newsgroup in place of an education -- actually read Codd, Date,
Dijkstra, et al before you post. Not the high level stuff, but at
least the foundations so you do not sound like a "flat-earther" looking
for a kludge.

>> Perhaps though I am missing your point celko. If so, could you be more precise? <<

Get a copy of DATA & DATABASES for the philosophy of RDBMS,
grasshopper. Come back and I will beat you with a stick some more!