ordering records with blank fields last

G

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

Bob Barrows [MVP]

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
 
B

Bob Barrows [MVP]

Bob said:
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
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top