Need help on SQL - 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 2005 Forums

Transact-SQL (2005)

Need help on SQL

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-07-18 : 10:21:05

My table and data as follow,

declare @t2 table
(idx int identity, Asset_idx int, MaintDte datetime);
insert into @t2 values(1,'7/17/2010 7:45:12 AM');
insert into @t2 values(1,'7/20/2010 5:15:24 AM');
insert into @t2 values(2,'7/20/2010 7:45:12 AM');
insert into @t2 values(2,'7/16/2010 7:45:12 AM');
insert into @t2 values(3,'7/18/2010 7:45:12 AM');

I want my output will display the latest MaintDte for each Asset_idx. As a result, my output as follow,

idx | Asset_idx | LastMaintDte
--------------------------------------------
2	1	2010-07-20 05:15:24.000
3	2	2010-07-20 07:45:12.000
5	3	2010-07-18 07:45:12.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-18 : 10:23:53

[code]
select *
from
(
select *, row_no = row_number() over (partition by Asset_idx order by MaintDte desc)
from @t2
) r
where r.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-07-18 : 10:46:45

it's possible to avoid using row_number() over ... ??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-18 : 11:01:52

quote:
Originally posted by Delinda

it's possible to avoid using row_number() over ... ??

what's wrong with using that ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-18 : 11:05:43

here is an alternate way. But if you have more than 1 record with the same maximum MaintDte per Asset_idx, you will get those records returns. The row_number() method will not . . It will only return 1 record per Asset_idx


select	t2.*
from	@t2 t2
	inner join
	(
		select	Asset_idx, MaintDte = max(MaintDte)
		from	@t2
		group by Asset_idx
	) tm	on	t2.Asset_idx	= tm.Asset_idx
		and	t2.MaintDte	= tm.MaintDte

KH
[spoiler]Time is always against us[/spoiler]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-07-18 : 11:09:09

tq sir

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-07-18 : 19:49:20

Solution using row_number(), presented by khtan is the right way.

If you don't like row_number() just because of it's bad look

, you may have another solution having better look.

select * from @t2 where MaintDte in (select max(MaintDte) from @t2 group by Asset_idx)

But keep in mind, solution with row_number() is much better performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources