SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 get records based on lastet date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deepak_Dotnet
Starting Member

32 Posts

Posted - 06/13/2007 :  06:28:42  Show Profile  Reply with Quote
I have View which display date :

Id | RegID | CreatedDate |ApplicationNo | statusId

196ec71f | 3b5b0a | 2007-06-12 | APP_10 | 1
5f5bbfaa | 3b5b0a |2007-06-13 |APP_12 | 0
ced3196b | a2362 | 2007-06-06 | APP_05 | 1
eb80aba4 | b1111 |2007-06-05 |APP_06 | 2
466e-b7c | a2362 | 2007-06-09 | APP_03 | 1
98b7cb6e | c5777 | 2007-05-13 | APP_01 | 3


Here i have RegId "3b5b0a" having two record created at 2007-06-12 & 2007-06-13
and RegId "a2362" having two records created at 2007-06-09 & 2007-06-06


so now i want a query which retireve distinct RegId (if there is duplication) with latest created date.

my expected result is

Id | RegID | CreatedDate | ApplicationNo | statusId


5f5bbfaa | 3b5b0a | 2007-06-13 | APP_12 | 0
eb80aba4 | b1111 | 2007-06-05 | APP_06 | 2
466e-b7c | a2362 | 2007-06-09 | APP_03 | 1
98b7cb6e | c5777 | 2007-05-13 | APP_01 | 3

mainly check is based on created date(latest date) if two same RegID is having different created date.
created date include time as well along with date....

Edited by - deepak_Dotnet on 06/13/2007 06:55:40

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/13/2007 :  07:37:15  Show Profile  Reply with Quote

DECLARE	@Temp TABLE
(
	Id		varchar(8),
	RegID		varchar(6),
	CreatedDate	datetime,
	ApplicationNo	varchar(6),
	statusId	int
)

INSERT INTO @Temp
SELECT	'196ec71f', '3b5b0a', '2007-06-12', 'APP_10', 1 UNION ALL
SELECT	'5f5bbfaa', '3b5b0a', '2007-06-13', 'APP_12', 0 UNION ALL
SELECT	'ced3196b', 'a2362', '2007-06-06', 'APP_05', 1 UNION ALL
SELECT	'eb80aba4', 'b1111', '2007-06-05', 'APP_06', 2 UNION ALL
SELECT	'466e-b7c', 'a2362', '2007-06-09', 'APP_03', 1 UNION ALL
SELECT	'98b7cb6e', 'c5777', '2007-05-13', 'APP_01', 3

SELECT	*
FROM	@Temp AS T1
	JOIN
	(
		SELECT	RegID,
			[MAX_CreatedDate] = MAX(CreatedDate)
		FROM	@Temp
		GROUP BY RegID
	) AS T2
		 ON T2.RegID = T1.RegID
		AND T2.MAX_CreatedDate = T1.CreatedDate

Next time please provide the DDL and sample data to save other people time

Kristen
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts

Posted - 06/13/2007 :  08:07:15  Show Profile  Reply with Quote
Hi

I myself got the solution after doing lot of thinking ...

Select * from vw_table v1 where createddate in (Select MAX(CreatedDate) from vw_table as v2 where v2.RegID = v1.RegID)

Plz guys let me know if it wrong ...

Thanks to Kristen for reply ...

Thank u

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 06/13/2007 :  08:12:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Using same sample data as Kristen...
Select 	Id, RegID, CreatedDate, ApplicationNo, statusId	from
( Select *, row_number() over (partition by regid order by createddate desc) as recid from @temp
)
As d
where recid = 1

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 06/13/2007 08:13:14
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/13/2007 :  08:27:31  Show Profile  Reply with Quote
"Select * from vw_table v1 where createddate in (Select MAX(CreatedDate) from vw_table as v2 where v2.RegID = v1.RegID)"

Almost certainly an identical Query Plan to my example. Although the actual indexes available might make a difference I suppose.

I presume that Peso's will be more efficient, but would need much more data to test that.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000