Hi there...

If I have a table with two differnet DateTime variables, and I want to order the hits in date order. The problem is that I want to order them depending of wich of theese two columns are the highest(eg. latest date)

ex:
id | date_1 | date_2
------------------
1 | 1991 | 2001
2 | 2002 | 1991
3 | 1993 | 1992

should result in(highest first):
id
---
2
1
3

How would I do this?

If your DBMS has a function GREATEST (or similar) then:

ORDER BY GREATEST (date_1, date_2)

If not, you can do it using CASE:

ORDER BY CASE WHEN date_1 > date_2 THEN date_1 ELSE date_2 END

tony, you forgot the DESC

http://www.dbforums.com/t999138.html

zcumbag, please do not cross-post

Thanks, Rudy!

thanks... works fine...

sorry about the cross-posting. I missread thestartpage... wont happen again... thanks

OK now I deicovered another problem... I need to SELECT DISTINCT and because of that I can't have the CASE-statement in the ORDER BY-statement...

I need to find another solution...

is it posible to have the datestatement in the SELECT-line?
something like this:

SELECT DISTINCT TOP 10 ArticleId, Heading, WICHEVERISBIGGEST(date1, date2) AS date3 FROM tblARticles
...
ORDER BY date3

I haven't seem to find any WICHEVERISBIGGEST-kindof function...

anyone?

Well, if your DBMS doesn't have a "WHICHEVERISBIGGEST" function (in Oracle it is called GREATEST), then again CASE will do it:

SELECT DISTINCT TOP 10 ArticleId, Heading, CASE WHEN date1 > date2 THEN date1 ELSE date2 END AS date3

can you show us the query? i don't see why DISTINCT means that you can't put a CASE expression into the ORDER BY

you can also say ORDER BY 4 (where 4 is the 4th column) if that helps

The reason is Error 145 wich says Order By items must appear in the select list if Select Distinct is specified.

But it works if I put the case statement in the select line.

thanks