?
Solved

Help with getting last record for joined tables

Posted on 2011-09-28
6
Medium Priority
?
221 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

762 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