Check multiple rows for value, return only row with MAX/MIN

Question

I'm trying to write a query that will compare the value of N amount of rows and return only the row with the max value. For example, if I wanted to only return a table with non-duplicate rows, but only the row with the newest date -

key  | name  | value |   date
 1   | frank |  100  | 1/1/2013
 2   | peter |  200  | 2/1/2013
 3   | jonny |  300  | 3/1/2013
 4   | jonny |  300  | 4/1/2013

And the query:

SELECT key, name, value, MAX(date)
FROM myTable
WHERE key IN (1,2,3,4)

I'd be expecting this to return

key  | name  | value |   date
 1   | frank |  100  | 1/1/2013
 2   | peter |  200  | 2/1/2013
 4   | jonny |  300  | 4/1/2013

I am unsure how to use GROUP BY, I think I'm missing something fundamental with my attempts at it.

Answer 1

Well if you only want the newest row you could use the following:

SELECT TOP 1 key, name, value, date
FROM myTable
ORDER BY date desc

This should return the one row with the newest date in that table.

If you wanted the newest date for each name you could use group by:

SELECT name, max(date)
FROM myTable
WHERE key in(1,2,3,4)
GROUP BY name

Max is an aggregate function. Anytime you use an aggregate function any columns that are not being aggregated have to be specified in the group by clause.

So based on your expected results you probably want this:

;with namesWithMaxDate as(
 select
   name
  ,max(date) as date
 from
   myTable 
 group by
   name 
 )
 select 
   myTable.[key]
  ,myTable.name
  ,myTable.value
  ,myTable.date
 from myTable
 inner join
 namesWithMaxDate 
 on 
   myTable.name = namesWithMaxDate.name and 
   myTable.date = namesWithMaxDate.date

This is slightly more complex because you have columns that you want returned that are not included in the grouping. Hence two statements to arrive at the final result set.

Final option: good old fashioned sub-query.

 select 
   myTable.[key]
  ,myTable.name
  ,myTable.value
  ,myTable.date
 from myTable
  inner join
 (   select
       name
      ,max(date) as date
     from
       myTable 
     group by
       name ) as namesWithMaxDate
 on 
   myTable.name = namesWithMaxDate.name and 
   myTable.date = namesWithMaxDate.date

More here about aggregate functions. More here about group by.

Answer 2

Try This one:

SELECT a.key, a.name, a.value, a.date
FROM myTable a 
WHERE a.key IN (1,2,3,4)
and 
a.DATE = (select MAX(date) from myTable b where a.key = b.key)