Author |
Topic |
nevin2007
Starting Member
2 Posts |
Posted - 2012-12-21 : 03:44:55
|
HiI have a table like this :id ,date ,value11571283, 1/2/2012, 311571283, 1/2/2012, 411571283, 1/2/2012, 311571283, 1/3/2012, 411571283, 1/3/2012, 311571283, 1/3/2012, 411586401, 1/2/2012, 511586401, 1/2/2012, 511586401, 1/2/2012, 5etc ...How can i write an sql statement that return this :11571283, 1/2/2012, 311586401, 1/2/2012, 511571283, 1/3/2012, 4I mean last value of each id for each day.thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 03:47:01
|
do you've a unique id field or pk field? otherwise how do you determine the "last value" for a day? as you dont have timepart stored------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nevin2007
Starting Member
2 Posts |
Posted - 2012-12-21 : 03:55:40
|
@ visakh16I can add a column like this :id ,date ,value,recnum11571283, 1/2/2012, 3,111571283, 1/2/2012, 4,211571283, 1/2/2012, 3,311571283, 1/3/2012, 4,411571283, 1/3/2012, 3,511571283, 1/3/2012, 4,611586401, 1/2/2012, 5,711586401, 1/2/2012, 5,811586401, 1/2/2012, 5,9 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-21 : 04:04:32
|
ok. then here you goSELECT id,date,value,recnumFROM(SELECT id,date,value,recnum,ROW_NUMBER() OVER (PARTITION BY id,[date] ORDER BY recnum DESC) AS SeqFROM Table)tWHERE Seq=1 if its below sql 2005 use thisSELECT t.*FROM Table tINNER JOIN (SELECT id,[date],MAX(recnum) AS Latest FROM table GROUP BY id,[date])t1ON t1.id = t.idAND t1.[date] = t.[date]AND t1.Latest = t.recnum ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-21 : 04:06:58
|
quote: Originally posted by nevin2007 @ visakh16I can add a column like this :id ,date ,value,recnum11571283, 1/2/2012, 3,111571283, 1/2/2012, 4,211571283, 1/2/2012, 3,311571283, 1/3/2012, 4,411571283, 1/3/2012, 3,511571283, 1/3/2012, 4,611586401, 1/2/2012, 5,711586401, 1/2/2012, 5,811586401, 1/2/2012, 5,9
SELECT is, date, valueFROM table t1JOIN (SELECT id, date, MAX(recnum) maxx FROM Table GROUP BY id, date )t2ON t1.id = t2.id AND t1.date = t2.date AND t1.recnum = t2.maxx--Chandu |
|
|
|
|
|