Results 1 to 5 of 5

Thread: Select order

  1. #1
    Join Date
    Jan 2010
    Posts
    9

    Unanswered: Select order

    Hello,

    I'm using the following SELECT:

    SELECT DISTINCT ticket.status, ticket.number, ticket.datum_status FROM ticket WHERE ticket.status IN(3,4) GROUP BY ticket.number ORDER BY ticket.datum_status DESC LIMIT 5;

    I get the following result:

    4, 'TRA-03-1050', '2010-01-17 13:21:57'
    3, 'TRA19-1010', '2010-01-15 12:22:08'
    3, 'TRA19-1000', '2010-01-15 12:16:24'

    The row values are correct, except for the datum_status, which returns the oldest date, instead of the newest.

    Any ideas on how to retrieve the newest?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ORDER BY ticket.datum_status ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    9
    Hello,

    That will only order by result:

    3, 'TRA19-1000', '2010-01-15 12:16:24'
    3, 'TRA19-1010', '2010-01-15 12:22:08'
    4, 'TRA-03-1050', '2010-01-17 13:21:57'

    What I need is to retrieve the last modified value of these rows:

    4, 'TRA-03-1050', '2010-01-17 13:21:57'
    4, 'TRA-03-1050', '2010-01-17 13:24:08'
    4, 'TRA-03-1050', '2010-01-17 13:24:15'
    4, 'TRA-03-1050', '2010-01-17 13:25:04'
    4, 'TRA-03-1050', '2010-01-17 13:25:20'
    4, 'TRA-03-1050', '2010-01-17 13:43:39'

    Any ideas?

    Thanks

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SELECT ticket.number, MAX(ticket.datum_status)
    FROM ticket
    WHERE ticket.status IN(3,4)
    GROUP BY ticket.number
    ORDER BY ticket.datum_status DESC
    LIMIT 5;
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jan 2010
    Posts
    9
    Bingo!

    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
  •