Best way to write this query?

[pandatime]

Hi,

My data set looks like:

id type date count
1 dogs 2010-01-01 1
1 dogs 2010-01-15 4
1 dogs 2010-01-31 10
1 cats 2010-02-02 4
1 cats 2010-02-16 7

What I want to do is write a query that will return the record that contains the max(date) for a given type AND sum up all the counts for a given type.

So I'd like my result set to be:

1 dogs 2010-01-31 15 (which is 1+4+10)
1 cats 2010-02-16 11 (which is 4+7)

What is the most efficient way to do this?

Thanks much

 

[r937]

SELECT t.type   
     , t.date
     , m.rows AS count
  FROM ( SELECT type
              , MAX(date) AS latest
              , COUNT(*) AS rows
           FROM daTable
         GROUP
             BY type ) AS m
INNER
  JOIN daTable AS t
    ON t.type = m.type
   AND t.date = m.latest

i've disregarded the "id" column because it appears to be constant

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Why derived table?

SELECT Id
     , type
     , MAX(date) AS latest
     , COUNT(*) AS rows
FROM daTable
GROUP BY Id, type

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

why a derived table? because the original request was to "return the record that contains the max(date)"

yes, in this instance there were no other columns involved, so it wasn't strictly necessary, but as soon as there is even one column that's not part of the GROUP BY key, you're in deep doodoo without the derived table to join back to



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Agreed.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.