Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pnpsql
Posting Yak Master
246 Posts |
Posted - 2013-03-19 : 11:46:51
|
hi team , i have table with follwoing data create table test1 (id numeric(10) , date1 datetime , total numeric(10)) insert into test1 values(1,'14-mar-2013',100), (2,'14-mar-2013',110), (3,'14-mar-2013',10), (4,'15-mar-2013',110), (5,'15-mar-2013',120), (6,'15-mar-2013',70) and i want to get maximum id for each date with correct total like 3 2013-03-14 00:00:00.000 10 6 2013-03-15 00:00:00.000 70 select max(id), date1, max(total) from test1 group by date1 it gives wrong recordchallenge everything |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-19 : 11:49:28
|
[code]SELECT id,date1,totalFROM(SELECT ROW_NUMBER() OVER (PARTITION BY date1 ORDER BY id DESC) AS Seq,*FROM test1)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-19 : 11:51:25
|
select t1.* from test1 as t1join(select max(id) as id, date1 from test1 group by date1)t2on t1.id = t2.id Too old to Rock'n'Roll too young to die. |
|
|
|
|
|
|
|