CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
Legion
Legion

Reputation: 3457

Using max and other SQL aggregate functions

Whenever I use SQL aggregate functions I find their utility hampered by the need for the Group By clause. I always end up having to use a bunch of nested selects to get what I want. I'm wondering if I'm just not using these functions correctly.

For instance. If I have the following data:

ID     Fruit     Color     CreatedDate
--     -----     -----     -----------
1      Apple      Red      2014-07-25 12:41:44.000
2      Apple      Green    2014-07-31 10:01:01.000
3      Apple      Blue     2014-07-10 07:05:51.317
4      Orange     Orange   2014-06-26 13:42:35.360

I want to get the most recently created apple record. If I use this:

SELECT [ID]
      ,[Fruit]
      ,[Color]
      ,max([CreatedDate])
  FROM [CCM].[dbo].[tblFruit]
  WHERE Fruit = 'Apple'
  GROUP BY ID, Fruit, Color

It gives me all three Apple entries, not just the latest one because I'm forced to include all the other columns in the group by clause. Really I just want it to group by fruit and give me the latest record (the whole record, not just a subset of the columns).

To get what I want I have to use this:

SELECT [ID]
      ,[Fruit]
      ,[Color]
      ,[CreatedDate]
  FROM [CCM].[dbo].[tblFruit]
  WHERE Fruit = 'Apple' AND CreatedDate IN
  (SELECT max([CreatedDate]) as [CreatedDate]
  FROM [CCM].[dbo].[tblFruit]
  WHERE Fruit = 'Apple')

This is ugly to me and it would be easier to just forget about aggregates in SQL and do any min, max, count, etc in .NET.

Is this the correct way to use aggregates (with nested selects) or am I doing it wrong?

Upvotes: 0

Views: 100

Answers (3)

Andrew
Andrew

Reputation: 8758

Based on your comment, you can use a CTE to build a list of max date for each fruit. Then you can join that back to your original table to get the full row that matches that max date.

SQL Fiddle

    with MaxDates as 
    (select 
     fruit,
     max(createddate) as maxdate
     from
     table1
     group by
     fruit)

    select
    t1.*
    from
    table1 t1
    inner join maxdates md
      on t1.fruit = md.fruit

  and t1.createddate = md.maxdate

BTW, you really don't want to try and push this kind of functionality to your application. Doing this kind of stuff is infinitely better in SQL. If nothing else, think about if you have millions of rows in your table. You certainly don't want to push those millions of rows from your db to your application to sum it up to a single row, etc.

Upvotes: 0

Taryn
Taryn

Reputation: 247880

For this situation you may be better off using a windowing function like row_number()

select id, fruit, color, createddate
from 
(
  select id, fruit, color, createddate,
    row_number() over(partition by fruit order by createddate desc) seq
  from tblFruit
) d
where seq = 1;

See Demo

Using this allows you to partition the data by the fruit and order the rows within each fruit by the createddate. By placing your row_number() inside of a subquery, you will return the first row of each fruit - these are the items with a seq=1. If you are looking for items that are only Apple, then you can easily add a WHERE clause.

You could also get the result by using a subquery to select the max(createddate) for each fruit:

select f.id,
  f.fruit,
  f.color,
  f.createddate
from tblFruit f
inner join
(
  select fruit, max(createddate) CreatedDate
  from tblfruit
  group by fruit
) d
  on f.fruit = d.fruit
  and f.createddate = d.createddate;

See Demo. You get the same result and you could still apply a WHERE filter to this.

Upvotes: 3

Ross Bush
Ross Bush

Reputation: 15185

How about using TOP with an ORDER BY

SELECT TOP(1) *
  FROM [CCM].[dbo].[tblFruit]
  WHERE Fruit = 'Apple'
  ORDER BY [CreatedDate] DESC

Upvotes: 0

Related Questions