Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marius
Starting Member
3 Posts |
 Posted - 2002-08-29 : 19:07:15
|
| Hi!I have a news-system on my homepage where some of the records in the Access-database contains a date and some not (the date-field is null). I want to make one recordset with all the records in a special order. The records without a date comes first, then the newest record and at last the oldest record based on the date-field.When i use "ORDER BY Date DESC" the records without a date is ordered in the end, and not in the beginning as i I want it to.I understand why this happend, but not how i could solve my problem using SQL. I could make two Recordsets an loops in my page, but hope there is an easier way.Thank you!!(I hope you understand my question. My English is not so good.) |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-08-29 : 20:07:13
|
| I believe there is a function in Access called IIF() and you could use this to put in a value for those that have no date, such that they would sort to the top. For example, you could put in a date far into the future, sort DESC, and they should show up first. |
 |
|
|
marius
Starting Member
3 Posts |
Posted - 2002-08-30 : 10:37:59
|
| Yes, that could be a solution, but the date-field can't contain a date, even if it is far into the future :)Since the Data Type for the field is set to Date/Time my only two alternativs is to put in a valid date or keep it empty.Thanks anywayOne thing I could do is to make two recordsets. One with the "no-date" records and one with the "date"-records in the right order. Then I can join them into a new recordset. My problem here is that I don't know a practical metod to join two-dimension arrays. Anyone?? (The hard way is to make a loop that transfer one record at a time, but there must be a better way to do it.) |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-08-30 : 11:14:11
|
| ORDER By Iif(datcol IS NULL,0,1), datecol DESC |
|
|
|
marius
Starting Member
3 Posts |
Posted - 2002-08-30 : 19:24:31
|
| Perfect!!Thank you! |
|
|
|
|
|
|
|
|
ORDER BY Date