get records based on lastet date

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 2005 Forums

Transact-SQL (2005)

get records based on lastet date

Author

Topic

deepak_Dotnet
Starting Member

32 Posts

Posted - 2007-06-13 : 06:28:42

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....

Kristen
Test

22859 Posts

Posted - 2007-06-13 : 07:37:15

[code]
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
[/code]
Next time please provide the DDL and sample data to save other people time

Kristen

deepak_Dotnet
Starting Member

32 Posts

Posted - 2007-06-13 : 08:07:15

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-13 : 08:12:36

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

Kristen
Test

22859 Posts

Posted - 2007-06-13 : 08:27:31

"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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources