Retrieve row of aggregate on primary key with group by

aggregate, group-by, sql, sql-server, sql-server-2005

Solution

Make sure you have an index defined such as:

CREATE NONCLUSTERED INDEX idx_GroupBy ON mytable (value1, value2)

`IN` can be tend to be slow if you have many rows. It may help to turn your `IN` into an `INNER JOIN`.

SELECT
    data.*
FROM
    mytable data
        INNER JOIN
    (
        SELECT id = MAX(id) 
        FROM mytable 
        GROUP BY value1, 
                 value2
    ) ids
        ON data.id = ids.id

Unfortunately, Sql Server does not have any features that will do this any better.

Problem

Ok so I know you can't pull specific fields without an aggregate when you perform a SQL command with a group by, but it seems to me that if you are doing an aggregate on a primary key that is guaranteed to be unique, there should be a way to pull the other rows of that column along with it. Something like this: ``` SELECT Max(id), foo, bar FROM mytable GROUP BY value1, value2 ``` So ID is guaranteed to be unique, so it will have exactly 1 value for foo and bar, is there a way to generate a query like this? I have tried this, but MyTable in this case has millions of rows, and the run-time for this is unacceptable: ``` SELECT * FROM mytable WHERE id IN (SELECT Max(id) FROM mytable GROUP BY value1, value2) AND ... ``` Ideally I would like a solution that works at least as far back as SQL server 2005, but if there are better solutions in the later versions I would like to hear them as well.

Original source