ordering records with blank fields last

Discussion in 'ASP General' started by Guest, Nov 3, 2005.

  1. Guest

    Guest Guest

    Hi. I'm a sorta novice ASP programmer, so I sure could use some help
    from of you more experienced gurus....

    I've written an ASP page that pulls all records from a table. My query
    is 'SELECT * FROM teacher ORDER by Class'

    It is working fine... sorta. The table has about 20 records, but only
    5 have an entry in the 'Class' field. The 15 records with the blank
    class field show up first, then the other 5 records. Is it possible to
    have the blank one show after the ones with an entry. Here's how my
    results look, IOW.

    The 15 blank
    3A
    3B
    5B
    6A
    7D

    I would like it to show as:

    3A
    3B
    5B
    6A
    7D
    The 15 blank

    I tried using the DESC keyword, but as I imagined, the results were:

    7D
    6A
    5B
    3B
    3A
    The 15 blank

    So, can anyone help me out here? I hope I've made everything clear
    enough.
     
    Guest, Nov 3, 2005
    #1
    1. Advertisements

  2. Never, never, never (have I made it clear that this should never be done?
    <grin>) ask a database-related question without telling us what type and
    version of database you are using. It is almost always relevant, and
    definitely IS relevant for this question

    Bob Barrows
     
    Bob Barrows [MVP], Nov 3, 2005
    #2
    1. Advertisements

  3. Oops, I meant to provide an answer. My bad. For Access you would do this:

    "select ... order by iif([Class] > '',0,1), Class"

    For SQL Server, you would use CASE instead of the VBA Iif function:
    "select ... order by CASE WHEN Class>'' THEN 0 ELSE 1 END,Class"

    HTH,
    Bob Barrows
     
    Bob Barrows [MVP], Nov 3, 2005
    #3
  4. Guest

    Guest Guest

    I am using Access 2000, and your suggestion worked perfectly! Much
    thanks.
     
    Guest, Nov 3, 2005
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.