Select a date field in ascending order, but put NULL values last?

Select a date field in ascending order, but put NULL values last?

Post by P. Obbar » Fri, 25 Apr 2003 03:13:15



Hi all. This is question is obstensibly about Access, but deals with SQL
syntax, which is why I am cross-posting.

I've got a table called 'MyTable' with a date field called 'MyDate'. I want
to select the values from that field and display them in ascending order.
E.g.,

"SELECT MyDate FROM MyTable ORDER BY MyDate ASC"

However, it always puts the empty (Null) fields up top when it returns the
results. Is there any way I can convert the MyDate type in SQL so that the
Null fields appear at the bottom of the list?

Any help would be appreciated. Thanks!

--Philip

 
 
 

Select a date field in ascending order, but put NULL values last?

Post by Anith Se » Fri, 25 Apr 2003 03:19:55


Do:

ORDER BY COALESCE(MyDate, '99991231')

--
- Anith
( Please reply to newsgroups only )

 
 
 

Select a date field in ascending order, but put NULL values last?

Post by Bob Barrow » Fri, 25 Apr 2003 03:36:56


This would be a great solution in SQL Server, but would bomb in Access :-)

Philip should use Nz instead of COALESCE in his Access query.

Bob Barrows


> Do:

> ORDER BY COALESCE(MyDate, '99991231')

 
 
 

Select a date field in ascending order, but put NULL values last?

Post by Anith Se » Fri, 25 Apr 2003 03:54:02


I should've read the question first :-(

--
- Anith
( Please reply to newsgroups only )

 
 
 

Select a date field in ascending order, but put NULL values last?

Post by P. Obbar » Fri, 25 Apr 2003 05:19:51


Thanks Bob and Anith. Both Coalesce() and NZ() were new to me, and were
interesting to learn about. Unfortunately, I should have been more specific.
I'm using ADO via an ASP page to get to an MS Access DB. So I couldn't get
either function working.

But, by searching around for equivalents to those two methods, I finally
found a solution that works beautifully:

ORDER BY IIF(ISNULL(MyDate), #1/1/2050#, MyDate) ASC

The date value in 2050 works fine - at least until 2050, ha ha. But that's
for a future programmer to worry about.

Thanks again for your quick responses & help!

--Philip


> This would be a great solution in SQL Server, but would bomb in Access :-)

> Philip should use Nz instead of COALESCE in his Access query.

> Bob Barrows


> > Do:

> > ORDER BY COALESCE(MyDate, '99991231')

 
 
 

Select a date field in ascending order, but put NULL values last?

Post by Douglas J. Steel » Fri, 25 Apr 2003 06:20:37



Quote:> The date value in 2050 works fine - at least until 2050, ha ha. But that's
> for a future programmer to worry about.

That's why people suggested using #12/31/9999#.

That's the largest date Access can hold, so you shouldn't ever run into
problems with it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

 
 
 

1. How to sort order in SELECT by ascending/descending order for MS SQL

Here is a good example from SQL Server Books online
Hope this helps


The following statement sorts the rows in the titles table, first by
publisher in descending order, and then by type in ascending order within
each publisher, and finally by price (also ascending, because DESC is not
specified). Null values are sorted first within any group:
SELECT pub_id, type, title_id, price
FROM titles
ORDER BY pub_id desc, type, price

The number of the position of a column in a select list can be used instead
of the column name. You can mix column names and select list numbers. Both
of the following statements produce the same results as the preceding one:
SELECT pub_id, type, title_id
FROM titles
ORDER BY 1 DESC, 2, 3

SELECT pub_id, type, title_id
FROM titles
ORDER BY 1 DESC, type, 3

2. Exporting to another MDB file?

3. Null date values in Date/Time fields in Access DB

4. Ingres and storing text

5. Analytical Function: select last value that is not null

6. can't have direct access to database...

7. REQUIRE KEY FIELD IN ASCENDING ORDER

8. Jobs Again

9. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

10. Check for NULL field value without returning field value

11. Null value in date field

12. Allowing a date field to be null value

13. Update a DATE-field to a NULL-value