Solved

Help with getting last record for joined tables

Posted on 2011-09-28
6
218 Views
Last Modified: 2012-05-12
I have the following query which collects and joins data from 4 tables and it works -almost. The problem is that it returns too much data. I need it to just pull in the last record for each volume (label). I have included some sample resulting data. Note that while I don't show it there is a datetime field we migt be able to use if that will help...

Query:
SELECT Nodes.NodeID, Nodes.Caption, CP.Label, CD.Status
FROM Nodes
JOIN dbo.CustomPollerAssignment C
ON Nodes.NodeID = C.NodeID
JOIN dbo.CustomPollerLabels CP
ON CP.CustomPollerAssignmentID = C.CustomPollerAssignmentID
JOIN dbo.CustomPollerStatistics_Detail CD
ON CP.CustomPollerAssignmentID = CD.CustomPollerAssignmentID AND CP.RowID = CD.RowID
WHERE (C.AssignmentName like '%netappvol%' and CP.Label not like '%snapshot%')
AND CD.Status >= 65
GROUP BY Nodes.NodeID, Nodes.Caption, CP.Label, CD.Status
ORDER BY Nodes.Caption, CP.Label

Results in data like:
NodeID      Caption            Label      Status
214      NetApp1            /vol/apps      81
214      NetApp1            /vol/files      77
214      NetApp1            /vol/profile      87
214      NetApp1            /vol/var      72
268      CFPNetApp                           /TMP      69
58      NetApp2            /vol/vol/book      78
58      NetApp2            /vol/vol/limit      80
58      NetApp2            /vol/vol/limit      88 <<<<< Most recent record >>>>>
58      NetApp2            /vol/vol/book      87
58      NetApp2            /vol/vol/pool      100
58      NetApp2            /vol/vol/pool      97
58      NetApp2            /vol/vol/pool      88
58      NetApp2            /vol/vol/pool      95 <<<<< Most recent record >>>>>

Desired results:
NodeID      Caption            Label      Status
214      NetApp1            /vol/apps      81
214      NetApp1            /vol/files      77
214      NetApp1            /vol/profile      87
214      NetApp1            /vol/var      72
268      CFPNetApp                           /TMP      69
58      NetApp2            /vol/vol/book      78
58      NetApp2            /vol/vol/limit      88
58      NetApp2            /vol/vol/book      87
58      NetApp2            /vol/vol/pool      95


0
Comment
Question by:edrz01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 36719136
something is not right in your example. The above query cannot produce that result, but more something like this (note the order of the /vol/vol/book label).  

214      NetApp1            /vol/apps      81
214      NetApp1            /vol/files      77
214      NetApp1            /vol/profile      87
214      NetApp1            /vol/var      72
268      CFPNetApp                           /TMP      69
58      NetApp2            /vol/vol/limit      80
58      NetApp2            /vol/vol/limit      88 <<<<< Most recent record >>>>>
58      NetApp2            /vol/vol/book      78
58      NetApp2            /vol/vol/book      87
58      NetApp2            /vol/vol/pool      100
58      NetApp2            /vol/vol/pool      97
58      NetApp2            /vol/vol/pool      88
58      NetApp2            /vol/vol/pool      95 <<<<< Most recent record >>>>>

Now based on that how do you determine which one is the most recent? I guess you will have to do something like this




declare @t table (
	id int identity(1,1),
	Nodes int,
	Caption varchar(20),
	Label varchar(20),
	Status int
)

insert @t 
SELECT 	Nodes.NodeID, 
	Nodes.Caption, 
	CP.Label, 
	CD.Status
FROM Nodes
JOIN dbo.CustomPollerAssignment C ON Nodes.NodeID = C.NodeID
JOIN dbo.CustomPollerLabels CP ON CP.CustomPollerAssignmentID = C.CustomPollerAssignmentID
JOIN dbo.CustomPollerStatistics_Detail CD ON CP.CustomPollerAssignmentID = CD.CustomPollerAssignmentID AND CP.RowID = CD.RowID
WHERE 	(C.AssignmentName like '%netappvol%' and CP.Label not like '%snapshot%')
	AND CD.Status >= 65 AND
ORDER BY Nodes.Caption, CP.Label

select * from @t a
where a.id = (select max(id) from @t where label = a.label)

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 36719137

Select * from
(
SELECT Nodes.NodeID, Nodes.Caption, CP.Label, CD.Status
,Row_Number() Over(partition by nodes.nodeid, cp.label order by DATETIMEFIELDHERE desc) seq
FROM Nodes
JOIN dbo.CustomPollerAssignment C
ON Nodes.NodeID = C.NodeID
JOIN dbo.CustomPollerLabels CP
ON CP.CustomPollerAssignmentID = C.CustomPollerAssignmentID
JOIN dbo.CustomPollerStatistics_Detail CD
ON CP.CustomPollerAssignmentID = CD.CustomPollerAssignmentID AND CP.RowID = CD.RowID
WHERE (C.AssignmentName like '%netappvol%' and CP.Label not like '%snapshot%')
AND CD.Status >= 65
) as x
where x.seq = 1
ORDER BY Nodes.Caption, CP.Label

0
 
LVL 41

Expert Comment

by:ralmada
ID: 36719171
now if you have a datetime field
then you can try

;with cte as (
      SELECT       Nodes.NodeID,
            Nodes.Caption,
            CP.Label,
            CD.Status

      FROM Nodes
      JOIN dbo.CustomPollerAssignment C ON Nodes.NodeID = C.NodeID
      JOIN dbo.CustomPollerLabels CP ON CP.CustomPollerAssignmentID = C.CustomPollerAssignmentID
      JOIN dbo.CustomPollerStatistics_Detail CD ON CP.CustomPollerAssignmentID = CD.CustomPollerAssignmentID AND CP.RowID = CD.RowID
      WHERE       (C.AssignmentName like '%netappvol%' and CP.Label not like '%snapshot%')
            AND CD.Status >= 65 AND
)
select a.* from cte a
where a.datetimefield = (select max(datetimefield) from cte where nodeid = a.nodeid and label = a.label)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 36719204
oops missed to add the datetimefield in the cte part

;with cte as (
      SELECT       Nodes.NodeID,
            Nodes.Caption,
            CP.Label,
            CD.Status,
            datetimefield

      FROM Nodes
      JOIN dbo.CustomPollerAssignment C ON Nodes.NodeID = C.NodeID
      JOIN dbo.CustomPollerLabels CP ON CP.CustomPollerAssignmentID = C.CustomPollerAssignmentID
      JOIN dbo.CustomPollerStatistics_Detail CD ON CP.CustomPollerAssignmentID = CD.CustomPollerAssignmentID AND CP.RowID = CD.RowID
      WHERE       (C.AssignmentName like '%netappvol%' and CP.Label not like '%snapshot%')
            AND CD.Status >= 65 AND
)
select a.* from cte a
where a.datetimefield = (select max(datetimefield) from cte where nodeid = a.nodeid and label = a.label)
0
 

Author Comment

by:edrz01
ID: 36814098
dgmg, when I tried your solution I got an eror on the 'seq'.

ralmada, on your solutions I get an error regarding the ')' here:
AND
) <<<<<
select a.* from cte a

I was confused as I haven't ever seen a solution that started out as
;with

So I typed it in as you showed but no-go.

Ideas?
0
 

Author Closing Comment

by:edrz01
ID: 36814391
While it had a typo I gave credit for a great solution.

I had to take out the work 'AND' from

            AND CD.Status >= 65
)
select a.* from cte a

Thanks!!!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question