OP here. I deeply apologize to my brothers for wasting your time with a bad example. My code was, in fact, correct, it was the example that was bad, and you're both absolutely right to have criticized it. I've worked my way around it, but I'm going to explain things here so that there's a record of what I meant. (And for that matter, I'd still be curious what the correct solution is for DBIx::Class.)

This is still simplifying things, but I think I am now doing so correctly. Assume my table has only two columns that are relevant for this question: event_id, which is an autoincrement primary-key column and is guaranteed to be ordered chronologically, and name. I am trying to get the row objects for the most recent event for each name, which (because event_id is ordered chronologically) will be the max value of the event_id column for each name. Thus, if my table is 

+----------+-----------+--------+
| event_id | name      |  other |
+----------+-----------+--------+
| 1        | John      |  foo   |
| 2        | John      |  bar   |
| 3        | Mary      |  baz   |
| 4        | John      |  quux  |
| 5        | Kate      |  blah  |
| 6        | Mary      |  bling |
+----------+-----------+--------+

[download]
Then, my query SELECT MAX(event_id), name FROM events GROUP BY name; will (I believe) give me this, which is what I want: 
+---------------+------+
| max(event_id) | name |
+---------------+------+
|             4 | John |
|             5 | Kate |
|             6 | Mary |
+---------------+------+

[download]
My original question is how I can write a query in DBIx::Class to return an array of objects for these rows.

I ended up simply getting a $dbh, and plugging my raw SQL query into this to get an array of event_id values, and then I loop over this array and do a DBIC find() on each event_id to give me the row object I need, which is pretty much instantaneous since it's a primary-key query. I also realize that I should never want to get all the result objects in one array; there's a possibility of a large number of results, and getting them all at once might lead to a memory problem.

(Just to be completely clear about what's going on, I'm using SQL Server; and the reason I specifically need the DBIC object itself is that I'm making use of some other code, which asks for the object. So I have to retrieve the object to pass to this other method.)

In reply to Re: MAX and GROUP BY in DBIx::Class by Anonymous Monk
in thread MAX and GROUP BY in DBIx::Class by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.