March 21, 2006 at 1:50 pm
I have a database with several thousand pieces of equipment indenified with WS_NUM .Each time a piece of equipment
is inventoried
a record is added with a new auditid,WS_NUM and related data.
I am having trouble with a query to pull out the lastest record for each piece of equipment based on the
highest auditid for each WS_NUM
I hope this example shows the problem clearly:
create table testx (WS_NUM int,ComponentValue varchar(255),auditid int)
insert into testx values (129,'F521',147)
insert into testx values (129,'F521',1300)
insert into testx values (129,'F521',1301)
insert into testx values (129,'F52A',3818)
insert into testx values (180,'BC1026',190)
insert into testx values (180,'BC1026',2628)
insert into testx values (180,'BC102A',4182)
insert into testx values (204,'BH1303',205)
insert into testx values (204,'BH130A',7877)
insert into testx values (204,'BH1303',2798)
insert into testx values (204,'BH1303',7870)
I would like to select one record for each WS_NUM based on the highest auditid
The result set would be :
WS_NUM ComponentValue auditid
129 F52A 3818
180 BC102A 4182
204 BH130A 7877
any help or comments would be appreciated
March 21, 2006 at 3:11 pm
select *
from #TestX
where AuditID in (select max(AuditID)
from #TestX
Group by ws_Num)
Results
129 F52A 3818
180 BC102A 4182
204 BH130A 7877
March 21, 2006 at 3:12 pm
Select
A.WS_NUM, A.ComponentValue, A.auditid
From
testx A
Inner
Join
(
Select WS_NUM, Max(auditid) as auditid from testx Group by WS_NUM) as B
On
B.WS_NUM = A.WS_NUM and B.auditid = A.auditid
Is much more efficient.....
March 22, 2006 at 12:23 pm
Thank you both for your solutions.I was unable to make any futher progress on my own.
Both methods worked to narrow my orginal result set from 3733 rows with duplicates/outdated information
to 1042 usable records.
My example was a simplified statement of the problem.When I adapted your solutions to the actual tables these are
the resulting queries:
select
ComponentValue,WS_NUM,workstationauditid
from vWORKSTATION_COMPONENTS
where TypeCode='IPAddress'and ComponentValue like'xxx.xx.%'and
workstationauditid in (select max(workstationauditid)from vWORKSTATION_COMPONENTS
Group by ws_Num)
which executed in 13 seconds
select a.ws_num,a.componentvalue,a.workstationauditid
From vWORKSTATION_COMPONENTS a
Inner Join
(Select WS_NUM, Max(workstationauditid) as workstationauditid from vWORKSTATION_COMPONENTS Group by WS_NUM)
as b
On b.WS_NUM = b.WS_NUM and b.workstationauditid = a.workstationauditid
where a.TypeCode='IPAddress'and
a.ComponentValue like'xxx.xx.%'
order by a.ws_num
which executed in 23 seconds
both solutions are efficient enought for me and a few steps in the right direction
but here is some of what I left out in trying to explain or break down the problem
Some of my information resides in the Workstat table This was my orginal Query
select w.aws_num,w.[name],w.dept,w.location,w.tcpip,w.NODE_ADDR,
v.ComponentValue,v.WS_NUM,v.workstationauditid
from vWORKSTATION_COMPONENTS v join workstat w on w.WS_NUM=v.WS_NUM
where v.TypeCode='IPAddress'and
v.ComponentValue like'xxx.xx.%'
group by w.aws_num,w.[name],w.dept,w.location,v.ComponentValue,w.tcpip,v.WS_NUM,w.NODE_ADDR,v.workstationauditid
order by v.WS_NUM,v.ComponentValue
which returned 3733 rows in 14 seconds with all of the information I was looking for
efficent to execute but containing 60% garbage to sort through manually
so my next step was to try to Join the workstat table to your solutions
as below:
select w.aws_num,w.[name],w.dept,w.location,w.tcpip,w.NODE_ADDR, --from workstat w
v.ComponentValue,v.WS_NUM,v.workstationauditid
from vWORKSTATION_COMPONENTS v join workstat w on w.WS_NUM=v.WS_NUM
where v.TypeCode='IPAddress'and
v.ComponentValue like'xxx.xx.%'and v.workstationauditid in (select max(v.workstationauditid)
from vWORKSTATION_COMPONENTS v
Group by v.ws_Num)
----------------------------------------------------------------------------------------------
select w.aws_num,w.[name],w.dept,w.location,w.tcpip,w.NODE_ADDR,a.ws_num,a.componentvalue,a.workstationauditid
From TRACKIT65_DATA..workstat w join vWORKSTATION_COMPONENTS a
on w.WS_NUM=a.WS_NUM
Inner Join
(Select WS_NUM, Max(workstationauditid) as workstationauditid from vWORKSTATION_COMPONENTS Group by WS_NUM)
as b
On b.WS_NUM = b.WS_NUM and b.workstationauditid = a.workstationauditid
where a.TypeCode='IPAddress'and
a.ComponentValue like'xxx.xxx.%'
order by a.ws_num
Both of these Queries ran for over five minutes before I cancelled them
I am wondering if my join syntax is wrong or is it better to use two seperate queries and maybe a temp table or some other approach
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply