• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Help with getting last record for joined tables

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
edrz01
Asked:
edrz01
  • 3
  • 2
1 Solution
 
ralmadaCommented:
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
 
dqmqCommented:

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
 
ralmadaCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
ralmadaCommented:
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
 
edrz01Author Commented:
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
 
edrz01Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now