ordering records with blank fields last

Discussion in 'ASP General' started by feel_free_to_spam_me@yahoo.com, Nov 3, 2005.

  1. 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.
    , Nov 3, 2005
    #1
    1. Advertising

  2. wrote:
    > 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.


    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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Nov 3, 2005
    #2
    1. Advertising

  3. Bob Barrows [MVP] wrote:
    > wrote:
    >> 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.

    >
    > 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
    >


    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
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Nov 3, 2005
    #3
  4. Guest

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

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Q2hyaXM=?=

    re-ordering rows/records

    =?Utf-8?B?Q2hyaXM=?=, Jan 27, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    433
    Eliyahu Goldin
    Jan 30, 2005
  2. Patrick.O.Ige

    re-ordering rows/records in datagrid

    Patrick.O.Ige, Feb 8, 2006, in forum: ASP .Net
    Replies:
    6
    Views:
    8,146
    Patrick.O.Ige
    Feb 11, 2006
  3. nbigaouette

    Z-Ordering (Morton ordering) question

    nbigaouette, Nov 5, 2009, in forum: C Programming
    Replies:
    2
    Views:
    2,127
  4. Replies:
    0
    Views:
    411
  5. Achim Domma (SyynX Solutions GmbH)

    support for ordering records in rails

    Achim Domma (SyynX Solutions GmbH), Oct 22, 2005, in forum: Ruby
    Replies:
    2
    Views:
    144
    Eric Hodel
    Oct 23, 2005
Loading...

Share This Page