[rod k]

null will always be first/last (depending on sort order).

The only way I can think of is to use something like:

select foo,bar,etc,if(isnull(fruit),'zzzzz',fruit) as fruits from table order by fruits;

Then you'll have to check for 'zzzzz' and replace it.

Not elegent but will work, unless your field would ever have a bunch of z's at start normally.

[This message has been edited by rod k (edited November 12, 2000).]

[eaamj01]

SELECT *
FROM tblFruit
WHERE fruit IS NOT NULL
ORDER BY fruit;

That should do it if there are NULL values in fruit otherwise if fruit contains an empty string:

SELECT *
FROM tblFruit
WHERE fruit = ""
ORDER BY fruit;

Andy J

[rod k]

Read the ? again, Andy. He still wants the records with null values.