I give this sql request:
$sql = "SELECT * FROM incoming_email order by 'status','serial' limit
1 ";

where status is a 5 char varchar and serial is a mediumint and the
primary key.

There is an index
STATUS with no cardinality and fields: status, serial

The row returned is the one with the lower serial, but a higher status

eg serial status
269 held
is returned instead of
270 (status is blank)

How should the selection be written ?

bill

bill wrote:
> I give this sql request:
> $sql = "SELECT * FROM incoming_email order by 'status','serial' limit
> 1 ";
>
> where status is a 5 char varchar and serial is a mediumint and the
> primary key.
>
> There is an index
> STATUS with no cardinality and fields: status, serial
>
> The row returned is the one with the lower serial, but a higher status
>
> eg serial status
> 269 held
> is returned instead of
> 270 (status is blank)
>
> How should the selection be written ?
>
> bill

Is status blank or is it NULL?

On Sep 30, 3:35 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> bill wrote:
> > I give this sql request:
> > $sql = "SELECT * FROM incoming_email order by 'status','serial' limit
> > 1 ";
>
> > where status is a 5 char varchar and serial is a mediumint and the
> > primary key.
>
> > There is an index
> > STATUS with no cardinality and fields: status, serial
>
> > The row returned is the one with the lower serial, but a higher status
>
> > eg serial status
> > 269 held
> > is returned instead of
> > 270 (status is blank)
>
> > How should the selection be written ?
>
> > bill
>
> Is status blank or is it NULL?


This is a good question. It is never defined in the insert, but the
structure says that the value may not be null but no default value is
given.
A quick select using phpMyAdmin for status = NULL returns no records
and one where status = '' (that is two single quotes) returns a
record, so I presume it is blank.

bill wrote:
> On Sep 30, 3:35 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> bill wrote:
>>> I give this sql request:
>>> $sql = "SELECT * FROM incoming_email order by 'status','serial'
>>> limit 1 ";
>>
>>> where status is a 5 char varchar and serial is a mediumint and the
>>> primary key.
>>
>>> There is an index
>>> STATUS with no cardinality and fields: status, serial
>>
>>> The row returned is the one with the lower serial, but a higher
>>> status
>>
>>> eg serial status
>>> 269 held
>>> is returned instead of
>>> 270 (status is blank)
>>
>>> How should the selection be written ?
>>
>>> bill
>>
>> Is status blank or is it NULL?
>
>
> This is a good question. It is never defined in the insert, but the
> structure says that the value may not be null but no default value is
> given.
> A quick select using phpMyAdmin for status = NULL returns no records
> and one where status = '' (that is two single quotes) returns a
> record, so I presume it is blank.

Hold on a minute!

I just looked at your query again.

you are ordering by 'status', 'serial'

You have quoted the words and so are sorting by the strings "status" and
"serial"

bill <william@TechServSys.com> wrote in news:1191181320.074514.138890
@y42g2000hsy.googlegroups.com:

> On Sep 30, 3:35 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> bill wrote:
>> > I give this sql request:
>> > $sql = "SELECT * FROM incoming_email order by 'status','serial' limit
>> > 1 ";
>>
>> > where status is a 5 char varchar and serial is a mediumint and the
>> > primary key.
>>
>> > There is an index
>> > STATUS with no cardinality and fields: status, serial
>>
>> > The row returned is the one with the lower serial, but a higher status
>>
>> > eg serial status
>> > 269 held
>> > is returned instead of
>> > 270 (status is blank)
>>
>> > How should the selection be written ?
>>
>> > bill
>>
>> Is status blank or is it NULL?
>
>
> This is a good question. It is never defined in the insert, but the
> structure says that the value may not be null but no default value is
> given.
> A quick select using phpMyAdmin for status = NULL returns no records
> and one where status = '' (that is two single quotes) returns a
> record, so I presume it is blank.
>

> status = NULL
Nothing EVER "=" NULL

WHERE STATUS IS NULL might provide better/different results!

On Sep 30, 4:04 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> bill wrote:
> > On Sep 30, 3:35 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
> >> bill wrote:
> >>> I give this sql request:
> >>> $sql = "SELECT * FROM incoming_email order by 'status','serial'
> >>> limit 1 ";
>
> >>> where status is a 5 char varchar and serial is a mediumint and the
> >>> primary key.
>
> >>> There is an index
> >>> STATUS with no cardinality and fields: status, serial
>
> >>> The row returned is the one with the lower serial, but a higher
> >>> status
>
> >>> eg serial status
> >>> 269 held
> >>> is returned instead of
> >>> 270 (status is blank)
>
> >>> How should the selection be written ?
>
> >>> bill
>
> >> Is status blank or is it NULL?
>
> > This is a good question. It is never defined in the insert, but the
> > structure says that the value may not be null but no default value is
> > given.
> > A quick select using phpMyAdmin for status = NULL returns no records
> > and one where status = '' (that is two single quotes) returns a
> > record, so I presume it is blank.
>
> Hold on a minute!
>
> I just looked at your query again.
>
> you are ordering by 'status', 'serial'
>
> You have quoted the words and so are sorting by the strings "status" and
> "serial"

That fixed it - thanks.