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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help for "select" sql

Author  Topic 

nevin2007
Starting Member

2 Posts

Posted - 2012-12-21 : 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

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

nevin2007
Starting Member

2 Posts

Posted - 2012-12-21 : 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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 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/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-21 : 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
Go to Top of Page
   

- Advertisement -