CODEWITHSUNDEEP
sqlsql-serversql-server-2005group-byaggregate
Kevin DiTraglia
Kevin DiTraglia

Reputation: 26078

Retrieve row of aggregate on primary key with group by

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.

Upvotes: 4

Views: 5402

Answers (2)

Pete Carter
Pete Carter

Reputation: 2731

Try this:

SELECT foo
       ,bar
       ,MAX(ID) OVER(Partition By Value1, Value2 Order by Value1,Value2)
FROM myTable

CREATE NONCLUSTERED INDEX myIndex ON MyTable(Value1,Value2) INCLUDE(foo,bar)

This NCI will cover the whole query, so it will not need to lookup the table at all

Upvotes: -1

James Curtis
James Curtis

Reputation: 794

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.

Upvotes: 3

Related Questions