| Author |
 Topic  |
|
|
nevin2007
Starting Member
2 Posts |
 Posted - 12/21/2012 : 03:44:55
|
Hi
I have a table like this :
id ,date ,value
11571283, 1/2/2012, 3
11571283, 1/2/2012, 4
11571283, 1/2/2012, 3
11571283, 1/3/2012, 4
11571283, 1/3/2012, 3
11571283, 1/3/2012, 4
11586401, 1/2/2012, 5
11586401, 1/2/2012, 5
11586401, 1/2/2012, 5
etc ...
How can i write an sql statement that return this :
11571283, 1/2/2012, 3
11586401, 1/2/2012, 5
11571283, 1/3/2012, 4
I mean last value of each id for each day.
thanks
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
nevin2007
Starting Member
2 Posts |
Posted - 12/21/2012 : 03:55:40
|
@ visakh16
I can add a column like this :
id ,date ,value,recnum
11571283, 1/2/2012, 3,1
11571283, 1/2/2012, 4,2
11571283, 1/2/2012, 3,3
11571283, 1/3/2012, 4,4
11571283, 1/3/2012, 3,5
11571283, 1/3/2012, 4,6
11586401, 1/2/2012, 5,7
11586401, 1/2/2012, 5,8
11586401, 1/2/2012, 5,9
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 04:04:32
|
ok. then here you go
SELECT id,date,value,recnum
FROM
(
SELECT id,date,value,recnum,ROW_NUMBER() OVER (PARTITION BY id,[date] ORDER BY recnum DESC) AS Seq
FROM Table
)t
WHERE Seq=1
if its below sql 2005 use this
SELECT t.*
FROM Table t
INNER JOIN (SELECT id,[date],MAX(recnum) AS Latest
FROM table
GROUP BY id,[date])t1
ON t1.id = t.id
AND t1.[date] = t.[date]
AND t1.Latest = t.recnum
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/21/2012 : 04:06:58
|
quote:
Originally posted by nevin2007
@ visakh16
I can add a column like this :
id ,date ,value,recnum
11571283, 1/2/2012, 3,1
11571283, 1/2/2012, 4,2
11571283, 1/2/2012, 3,3
11571283, 1/3/2012, 4,4
11571283, 1/3/2012, 3,5
11571283, 1/3/2012, 4,6
11586401, 1/2/2012, 5,7
11586401, 1/2/2012, 5,8
11586401, 1/2/2012, 5,9
SELECT is, date, value
FROM table t1
JOIN (SELECT id, date, MAX(recnum) maxx
FROM Table
GROUP BY id, date )t2
ON t1.id = t2.id AND t1.date = t2.date AND t1.recnum = t2.maxx
--
Chandu |
|
|
| |
Topic |
|
|
|
help for "select" sql
