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.