Solved

Help with getting last record for joined tables

Posted on 2011-09-28
6
217 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
Import New Records From Access Table To SQL Database Table 7 30
TSQL Challenge... 7 35
Acces SQL Insert Problem 6 31
Stored Procedure needs owner to execute 5 13
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

840 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