ive got an ASPX web page accessing an MS ACCESS backend.

i need to retrieve a list of books buy catagory in alphabetical order.

ok simply.

"....order by catagory ASC"

However some books do not belong to a catagory. therfore they appear at the top of the list b4 the "A"s.

i want them to appear at the end of the list, after the ZZZZZs.

is there an SQL ORDER command that puts blanks at the end of a ASC list????

im sure ive heard of this! but cant remember the command!

====================================

No i dont want to use DESC

Are you using join? Show your sql query.

no im not using a join. what has a join got to do with it?

lets just pretend im doing select * from table1 order by row1.

i still want asc sorting with blanks at the end!!!!! time waster!!!!!!

You won't be able to place blanks after Zs because it's natural order is before As. If you need them after Zs you have to put them there within script.

I managed to do something with UNION ALL to get the nulls grouped at the end of the query, however it wasn't possible to sort the ones that were not null...

You may be to use a case statement to replace nothing with something.

select case category when '' then 'unknown' end as category from table order by category asc

thanks etech355, guys,

i think ill change nulls to zzz and get around it that way.



buy the way, any one interested might like to know that in oracle you can say:

order by XXXXX DESC NULLS LAST (or NULLS FIRST).
shame you cant do this in MS ACCESS.

thanks guys.

order by XXXXX DESC NULLS LAST (or NULLS FIRST).

Thats cool!

A union query will do what you want (if access supports UNION ) but the only way I can think of to make it work would be 26 UNION statements with wildcards from a to z

PHP Code:

select fieldlist from table where cat_field like "a%" 
union
select fieldlist from table where cat_field like "b%" 
union
select fieldlist  from table where cat_field = ""