ordering records with blank fields last

[feel_free_to_spam_me]

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.

[Bob Barrows [MVP]]

fe******************@yahoo.com 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]]

Bob Barrows [MVP] wrote:

fe******************@yahoo.com 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.

[feel_free_to_spam_me]

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