ordering records with blank fields last

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.

re: ordering records with blank fields last

feel_free_to_spam_me@yahoo.com wrote:[color=blue]
> 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.[/color]

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"

re: ordering records with blank fields last

Bob Barrows [MVP] wrote:[color=blue]
> feel_free_to_spam_me@yahoo.com wrote:[color=green]
>> 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.[/color]
>
> 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
>[/color]

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.

re: ordering records with blank fields last

I am using Access 2000, and your suggestion worked perfectly! Much
thanks.