Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: Order by problem

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tony, you forgot the DESC

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

    zcumbag, please do not cross-post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    tony, you forgot the DESC
    Thanks, Rudy!

  5. #5
    Join Date
    Apr 2004
    Posts
    31
    thanks... works fine...

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

  6. #6
    Join Date
    Apr 2004
    Posts
    31
    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?
    Last edited by Zcumbag; 05-26-04 at 08:52.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Zcumbag
    I need to SELECT DISTINCT and because of that I can't have the CASE-statement in the ORDER BY-statement...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Posts
    31
    Quote Originally Posted by r937
    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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •