locked
Using LAST with JOIN RRS feed

  • Question

  • User729047523 posted

    I'm retrieving data from two tables, PingEntry, and PingResult.

    PingEntry contains information about hosts, IP-adress and to wich user it belongs etc. PingResult contains the latency for each host in PingEntrys and date when the ICMP was executed.

    As I have an application that executes ICMP on a specific time interval, PingResult posts are constantly increasing.

    I'm using the following query to list all hosts for a specific user, and also data from the last ICMP execute(latency and date).

                    strSQL = "SELECT LAST(pingResult.pingDate) As LastDate, LAST(pingResult.LatencyResult) As LastPing," & _
                    " pingEntry.pingAdress, pingEntry.ID FROM pingResult" & _
                    " RIGHT JOIN pingEntry ON pingResult.peId = pingEntry.ID" & _
                    " WHERE PingEntry.userId = " & Session("userId") & " GROUP BY pingEntry.pingAdress, pingEntry.ID"

     

    Problem is, the latest pingDate and LatencyResult isnt displayed. If I add a new host, pingDate and LatencyResult gets updated a couple of times and then suddenly the values doesntupdate, even though new ICMP requests are being made.

    I can use MAX on pingDate to fix the date field, but that doesnt work for pingAdress as it would only show the highest latencyresult, even if thats week old data.

     

    Anyone who's got any suggestions?

     

    Regards

     

     

     

     

    Tuesday, December 2, 2008 7:33 PM

Answers

  • User1096912014 posted

    select PingEntry.IpAddress, PingResult.Date, PingResult.LatencyResult, PingResult.PingEntry INNER JOIN (select Max(date) as Latest_Date, pingEntry from PingResult group by pingEntry) LatestResult on PingResult.PingEntry = LatestResult.PingEntry and PingResult.Date = LatestResult.Latest_Date INNER JOIN  PingEntry on PingResult.PingEntry = PingEntry.PingEntry where PingEntry.UserID = @UserID

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 3, 2008 8:22 PM

All replies

  • User1096912014 posted

    SELECT L.LastDate, " & _
                    " pingEntry.pingAdress, pingEntry.ID FROM " & _
                    " pingEntry INNER JOIN (select Max(PingDate) as MaxDate, PeID from PingResult group by PeID) Result ON Result.peId = pingEntry.ID" & _
                    " WHERE PingEntry.userId = " & Session("userId") & " GROUP BY pingEntry.pingAdress, pingEntry.ID

    I think you can work along these lines. I'm not sure I understood exactly who is the parent and who is the child in these two tables and how they are related.

    Tuesday, December 2, 2008 10:56 PM
  • User729047523 posted

    SELECT L.LastDate, " & _
                    " pingEntry.pingAdress, pingEntry.ID FROM " & _
                    " pingEntry INNER JOIN (select Max(PingDate) as MaxDate, PeID from PingResult group by PeID) Result ON Result.peId = pingEntry.ID" & _
                    " WHERE PingEntry.userId = " & Session("userId") & " GROUP BY pingEntry.pingAdress, pingEntry.ID

    I think you can work along these lines. I'm not sure I understood exactly who is the parent and who is the child in these two tables and how they are related.

     

     

    Sorry if there was any confusion in my earlier post!

    There is in total 3 tables: userInfo, pingEntry and pingResult.

    Every user has their hosts, wich are stored in the pingEntry table. The pingEntry table contains the ip-adress, user ID and some other information.

    The ICMP(ping replies) requests are all stored in pingResult table, with the latencyresult, date and the pingEntry ID.

     

    Since the ICMP requests are executed on a specified time interval, the pingResult table can contain hundreds of post for each pingEntry. 

    I want to show all pingEntry data that belongs to a specific user, but only the last pingResult for each pingEntry.

    So the MAX property gives the last pingResult.pingDate, wich is what I want. But since each ping result can differ both up and down, its not possible to use MAX on the pingResult.LatencyResult column.

     

     

    Wednesday, December 3, 2008 12:30 PM
  • User1096912014 posted

    select PingEntry.IpAddress, PingResult.Date, PingResult.LatencyResult, PingResult.PingEntry INNER JOIN (select Max(date) as Latest_Date, pingEntry from PingResult group by pingEntry) LatestResult on PingResult.PingEntry = LatestResult.PingEntry and PingResult.Date = LatestResult.Latest_Date INNER JOIN  PingEntry on PingResult.PingEntry = PingEntry.PingEntry where PingEntry.UserID = @UserID

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 3, 2008 8:22 PM