Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Unanswered: SQL Syntax Problem - Find Maxumum Value of Distinct Group

    I'm trying to find maxumum value of column for each member of a distinct group.

    Sample Table:
    ================================================== ===
    | id | rid | report_date | report_date_formatted |
    | 1 | 1 | 1014267600 | 2002-02-21 |
    | 128 | 1 | 1202619600 | 2008-02-10 |
    | 158 | 2 | 1016082000 | 2002-03-14 |
    | 392 | 2 | 1203138000 | 2008-02-16 |

    The following SQL statement works fine:

    SELECT rid, MAX(report_date) AS maxreportdate FROM report_posts GROUP BY rid

    Output:
    =======================
    | rid | report_date |
    | 1 | 1202619600 |
    | 2 | 1203138000 |


    Here is the problem... If I try to add another column to the query, the extra column does not match the results. This is NOT working:

    SELECT rid, report_date_formatted, MAX(report_date) AS maxreportdate FROM report_posts GROUP BY rid

    Erroneous Output:
    ====================
    | rid | report_date | report_date_formatted |
    | 1 | 1202619600 | 2003-08-07 |
    | 2 | 1203138000 | 2007-04-10 |

    The first two columns (rid & report_date) are correct and as expected, BUT the final additional column is INCORRECT and does not correspond to the selected row.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it beats me why you even store the formatted date -- don't

    if this works --

    SELECT rid, MAX(report_date) AS maxreportdate FROM report_posts GROUP BY rid

    then so will this --
    Code:
    SELECT rid
         , MAX(report_date) AS maxreportdate 
         , FROM_UNIXTIME(MAX(report_date)) AS maxreportdateformatted 
      FROM report_posts 
    GROUP 
        BY rid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I agree with r937, it doesn't make much sense to store the same value twice in the same row, just formatted differently. That sounds like a job for the presentation layer.

    Having said that, it doesn't matter what value you have in the third column, your query won't work because you are using an aggregate function and the third column isn't, and can't be, in the group by clause. Instead, you need to use a correlated subquery:

    SELECT RID, REPORT_DATE, REPORT_DATE_FORMATTED
    FROM test.sample AS s
    WHERE (REPORT_DATE =
    (SELECT MAX(REPORT_DATE)
    FROM test.sample
    WHERE (s.RID = RID)))

    In my tests, this returned the expected result set.

    Hope this helps.

Posting Permissions

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