retrive latest record - SQL Server Forums

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

Transact-SQL (2000)

retrive latest record

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-22 : 12:23:53

i want to retrive the latest record based on field1 and field_date. i am also posting my table structure.
create table #temp
(
field1 varchar(10)
,field2 varchar(10)
,field3 varchar(10)
,field_date datetime

)

insert into #temp
select 'test','3','4','12/1/2006' union all
select 'test','2','3','12/24/2006' union all
select 'blah','2','3','12/24/2006'

EXPECTED RESULT:
test,2,3,12/24/2006
blah,2,3,12/24/2006

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-22 : 12:36:25

[code]
select t1.* from
#temp t1
join
(select Field1, max(Field_Date) as Field_Date
from #temp
group by Field1) t2
on
t1.Field1 = t2.Field1 and
t1.Field_Date = t2.Field_Date
Order by t1.Field1 desc
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-22 : 13:08:43

You need to be a bit more specific about your requirement, when you say "the latest record based on field1 and field_date", that could be just one record, but your example shows two. Do you mean, all the rows for te latest date ordered by field1 or what?

Here's one guess
select Field1, Field_Date
from
(select top 1 Field1, Field_Date
from #temp
order by Field1 desc, Field_Date desc) Q1
union
select Field1, Field_Date
from
(select top 1 Field1, Field_Date
from #temp
order by Field_Date desc, Field1) Q2

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-22 : 13:32:30

thanks guys i tried harsh's solution and it worked

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-22 : 13:37:22

What results do you regard as correct if the data is like this, are either of our solutions correct?

create table #temp
(
field1 varchar(10)
,field2 varchar(10)
,field3 varchar(10)
,field_date datetime

)

insert into #temp
select 'test','3','4','12/1/2006' union all
select 'fred','3','4','12/1/2006' union all
select 'blah','3','4','12/1/2006' union all
select 'test','2','3','12/24/2006' union all
select 'blah','2','3','12/24/2006'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources