Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: How to select latest data(varchar) on group?

    I know mysql but I'm new to postgre. I found grouping is a bit different. If you have a field in select then you need to identify on the group as well.

    Here's the problem, I want to select the latest data and here's the table data.

    items table
    transid, memberid, item, date, status
    123, 1234, apple, 2009-11-23, PROCESSING
    124, 1234, orange, 2009-11-23, PROCESSING
    124, 1234, orange, 2009-11-24, COMPLETED

    This is just an example and please don't suggest me data structure to change to update 2nd row to COMPLETED.

    I want to able to display different item and the latest date and status.

    SELECT transid, item, max(date), status
    FROM items WHERE memberid=1234
    GROUP BY transid, item, status

    But the result will give me this.
    123, apple, 2009-11-23, PROCESSING
    124, orange, 2009-11-24, PROCESSING

    How to I make the status to display the latest "COMPLETED"? Maybe, using a different select query? Please help me.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by helloworld7
    If you have a field in select then you need to identify on the group as well.
    Which is true for all databases except MySQL. MySQLs "relaxed" way of dealing with GROUP BY ignores the standard and the fact that not having those columns in the group by can return undefined results.

    How to I make the status to display the latest "COMPLETED"? Maybe, using a different select query? Please help me.
    This should work:
    Code:
    SELECT i1.transid, 
           i1.item, 
           i1.date, 
           i1.status
    FROM items i1
    WHERE i1.memberid=1234
    AND date = (SELECT max(date)
                FROM items i2
                WHERE i2.transid = i1.transid 
                  AND i2.memberid = i1.memberid)
    The above query will not work as it is because date is a reserved word.
    I highly recommend you change the column name to e.g. status_date to avoid trouble. Otherwise you must enclose date in double quotes.

  3. #3
    Join Date
    Nov 2009
    Posts
    1
    I know mysql but I'm new to postgre. I found grouping is a bit different. If you have a field in select then you need to identify on the group as well.

  4. #4
    Join Date
    Dec 2009
    Posts
    1
    Quote Originally Posted by shammat View Post
    Which is true for all databases except MySQL. MySQLs "relaxed" way of dealing with GROUP BY ignores the standard and the fact that not having those columns in the group by can return undefined results.


    This should work:
    Code:
    SELECT i1.transid, 
           i1.item, 
           i1.date, 
           i1.status
    FROM items i1
    WHERE i1.memberid=1234
    AND date = (SELECT max(date)
                FROM items i2
                WHERE i2.transid = i1.transid 
                  AND i2.memberid = i1.memberid)
    The above query will not work as it is because date is a reserved word.
    I highly recommend you change the column name to e.g. status_date to avoid trouble. Otherwise you must enclose date in double quotes.
    Nice thread - some good stuff to consider.

Posting Permissions

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