#1
  1. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    i'm trying to get

    apple
    banana
    orange
    NULL
    NULL
    NULL

    instead of

    NULL
    NULL
    NULL
    apple
    banana
    orange

    -- possible?

    [This message has been edited by RyanP (edited November 12, 2000).]
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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).]
  4. #3
  5. No Profile Picture
    RyanP
    Guest
    Devshed Newbie (0 - 499 posts)
    hehe, not elegant indeed, but a nice work around, thanks for the idea, i'll let you know if i run into anything better
  6. #4
  7. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Colchester, England
    Posts
    131
    Rep Power
    15
    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
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Read the ? again, Andy. He still wants the records with null values.

IMN logo majestic logo threadwatch logo seochat tools logo