Hi all
Using sql server 2005, Im trying in a query to get the maximum value of multiple columns of a table for each of its records.
What im trying to get is the last date an index was used using the table sys.dm_db_usage_stats using the date fields (last_user_seek, last_user_update...).
I looked around the forum for a solution but those i found dont seem to apply really easily to a query.
Anyone got a suggestion?
Dale:
There have been a number of discussions about a similar issue; give a look to this post:
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=733186&SiteID=1
Dale L.,
Try using new operators UNPIVOT and "CROSS APPLY".
Code Snippet
create table dbo.t1 (
pk int not null identity unique,
c1 int,
c2 int,
c3 int
)
go
insert into dbo.t1(c1, c2, c3) values(1, 2, 3)
insert into dbo.t1(c1, c2, c3) values(4, 6, 5)
insert into dbo.t1(c1, c2, c3) values(9, 7, 8)
go
select
a.pk,
b.max_value
from
dbo.t1 as a
cross apply
(
select
max(unpvt.[value]) as max_value
from
(
select
t.pk,
t.c1 as [1],
t.c2 as [2],
t.c3 as [3]
from
dbo.t1 as t
where
t.pk = a.pk
) as pvt
unpivot
([value] for [col] in ([1], [2], [3])) as unpvt
group by
unpvt.pk
) as b
go
drop table dbo.t1
go
AMB
