Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: SELECT one from each?

    Table like this:
    date | id | value
    2003-10-01 | A | 100
    2003-10-02 | A | 300
    2003-10-03 | A | 200
    2003-10-01 | B | 300
    2003-10-02 | B | 100
    2003-10-02 | C | 200

    Should return result like this:
    date | id | value
    2003-10-03 | A | 200
    2003-10-02 | B | 100
    2003-10-02 | C | 200

    Point is to select the newest record (by date) for each of the id (only one for each id)

    SELECT DISTINCT... will return all records.

    SELECT ... ORDER BY date DESC LIMIT 1 will return just 1 record.

    SELECT MAX(date), id, value FROM table GROUP BY id
    will return unpredictable (some times correct) result.

    Any Solutions?

    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Don't know much about MySQL, but your syntax

    SELECT MAX(date), id, value FROM table GROUP BY id

    would be invalid in MSSQL and Oracle. The column "value" should be included in the GROUP BY expression, or removed from the select list.

    Regards.

    CVM.

  3. #3
    Join Date
    Jul 2003
    Posts
    30

    Re: SELECT one from each?

    The below should work so long as the value is always three in length, or you could rewrite this otherwise.

    select id,
    max(date) as date,
    right( max( concat(date, value)) ,3) as value
    from test
    group by id;


    http://www.mysql.com/doc/en/example-...group-row.html

Posting Permissions

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