SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question An odd sort by date problem...

    Hey folks,

    Hoping someone can provide me with a quick solution to an odd problem im having. I have the following code that displays records out of a shipping database, it groups boats by their status, then sorts by arrival date in ascending order. The problem is it actually puts the visits for the next month before the visits of the current month... quite odd

    PHP Code:
    ORDER BY (CASE t1.VisitStatus when 'Departed Vessel' then 1 when 'At Anchor' then 2 when 'In Port' then 3 else 4 end), ArrivalDate ASC 
    any help would be greatly appreciated

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is your arrivaldate an actual date type field or is it varchar or char perhaps?

    are your visitstatus rows in the correct order in respect to your case statement but then not ordered by arrivaldate within those groups?

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry dave, i should have posted the whole code to begin with

    PHP Code:
    code removed 
    the arrival/departure columns are datetime format
    Last edited by sennan; Jan 19, 2006 at 21:20.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    include the t1.Arrival column (unformatted) in the SELECT, then use it in the ORDER BY instead of ArrivalDate

    the problem was, you're sorting by the formatted date -- of course '02/24/2006' comes before '03/15/2005'

    and i'll bet you a beer you don't need DISTINCT -- speed up your query significantly by removing it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by daveman
    are your visitstatus rows in the correct order in respect to your case statement but then not ordered by arrivaldate within those groups?
    and yes that is correct.

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    include the t1.Arrival column (unformatted) in the SELECT, then use it in the ORDER BY instead of ArrivalDate

    the problem was, you're sorting by the formatted date -- of course '02/24/2006' comes before '03/15/2005'

    and i'll bet you a beer you don't need DISTINCT -- speed up your query significantly by removing it
    D'oh! of course... Thanks very much r937! I changed it to

    PHP Code:
    <?php echo date("d/m/Y H:i:s",strtotime($row['ArrivalDate']));?>
    and it's now working like a charm! I owe you a beer


Bookmarks

Posting Permissions

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