By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,978 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,978 IT Pros & Developers. It's quick & easy.

ordering records with blank fields last

P: n/a
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 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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"
Nov 3 '05 #2

P: n/a
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.
Nov 3 '05 #3

P: n/a
I am using Access 2000, and your suggestion worked perfectly! Much
thanks.

Nov 3 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.