?
Solved

Help with getting last record for joined tables

Posted on 2011-09-28
6
Medium Priority
?
225 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
  • 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 41

Accepted Solution

by:
ralmada earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

809 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