1

Using SQL Server 2000

I want to get the max(date) of total for each id.

ID Date Total

01 02/01/2012 500
01 01/02/2012 1000
01 02/03/2012 350
02 17/01/2012 250
02 15/02/2012 150
03 01/12/2011 225
...
...

I want to get max(date) of total for each id.

Tried Query

Select id, total from table1 where date > max(date) group by id, total

Getting error message as

"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Expected Output

ID Date Total

01 02/03/2012 350
02 15/02/2012 150
03 01/12/2011 225
...
...

How to do this.

Need Query Help

Andomar
  • 232,371
  • 49
  • 380
  • 404
JetJack
  • 978
  • 8
  • 26
  • 51

3 Answers3

4
Select id, date, total 
from table1 t
where date = (select max(date) from table1 where id = t.id
group by id)
Vikram
  • 8,235
  • 33
  • 47
3

This should work for you:

select *
from total t inner join
    (   select id, max(date) as date
        from total
        group by id ) m on t.id = m.id and t.date = m.date
John N
  • 1,755
  • 17
  • 21
0

This query will work 

select * from dbo.Table t1
where Date >= (select max(Date) from dbo.Table t2
where t1.ID = t2.ID)
Kanishka
  • 71
  • 3