Retrieve only the last record for variable

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

SQL Server 2008 Forums

Transact-SQL (2008)

Retrieve only the last record for variable

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-09-21 : 13:45:40

Hi i have a table with ID, start_date, end_date
and i want to retrieve only the last record for that record
eg:
ID start_date end_date
03T035 2000-06-01 00:00:00 2000-10-03 00:00:00
03T035 2000-10-04 00:00:00 2000-10-05 00:00:00
03T035 2001-06-01 00:00:00 2045-12-31 00:00:00
053026 1985-04-01 00:00:00 1986-03-31 00:00:00
053026 1986-04-01 00:00:00 1987-03-31 00:00:00

i want to retrieve only the following

03T035 2001-06-01 00:00:00 2045-12-31 00:00:00
053026 1986-04-01 00:00:00 1987-03-31 00:00:00

Thanks
JKR

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 13:49:01

[code]
SELECT ID, start_date, end_date
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY start_Date DESC) AS Seq,*
FROM Table
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-09-21 : 14:26:11

It says ROW_NUMBER is not a recognized function since the Database is in 2000 and i have sql 2008 on my system
Any other way without using the function

thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 14:35:04

I'm not sure how to interprete the plan for yours....mine has less to do though..


CREATE TABLE myTable99([ID] varchar(20), [start_date] datetime,  end_date datetime
, PRIMARY KEY ([ID], [start_date]))
GO

INSERT INTO myTable99 ([ID], [start_date], [end_date])

SELECT '03T035', '2000-06-01 00:00:00', '2000-10-03 00:00:00' UNION ALL
SELECT '03T035', '2000-10-04 00:00:00', '2000-10-05 00:00:00' UNION ALL
SELECT '03T035', '2001-06-01 00:00:00', '2045-12-31 00:00:00' UNION ALL
SELECT '053026', '1985-04-01 00:00:00', '1986-03-31 00:00:00' UNION ALL
SELECT '053026', '1986-04-01 00:00:00', '1987-03-31 00:00:00'
GO

SELECT *
  FROM myTable99 o
 WHERE EXISTS (
		  SELECT [ID]
		    FROM myTable99 i
		   WHERE i.[ID] = o.[ID]
		GROUP BY [ID]
		  HAVING MAX(i.[start_date]) = o.[start_date])
		  
SELECT ID, start_date, end_date
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY start_Date DESC) AS Seq,*
FROM myTable99
)t
WHERE Seq=1

		  
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-09-21 : 14:45:45

thanks
I am just using this

SELECT *
FROM myTable99 o
WHERE EXISTS (
SELECT [ID]
FROM myTable99 i
WHERE i.[ID] = o.[ID]
GROUP BY [ID]
HAVING MAX(i.[start_date]) = o.[start_date])

JKR

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 14:54:15

quote:
Originally posted by jayram11

thanks
I am just using this
...use [ code] [ /code] tags...without the space

SELECT *
FROM myTable99 o
WHERE EXISTS (
SELECT [ID]
FROM myTable99 i
WHERE i.[ID] = o.[ID]
GROUP BY [ID]
HAVING MAX(i.[start_date]) = o.[start_date])

JKR

..and whattdyamean..."just"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources