[SOLVED] Selecting the latest Row from a table in a Join Query

[cooldude832]

I have 3 tables being used in this query

 

Users (UserID is primary key all you need to know here)

Urls (UrlID, Url, UserID (links to Usres)

Urls_checks (BatchID, CheckID(PK) UrlID (links to urls) and a ton of cURL response fields)

 

My query right now looks like

Select urls.UrlID as UrlID , urls.Url as Url , urls.Active as Active , COUNT(urls_checks.UrlID) as Checks_Count ,
MAX(urls_checks.Date) as Last_Check , 
urls_checks.http_code as Last_Code ,
urls_checks.total_time as Last_Time 
from `urls` 
LEFT JOIN urls_checks ON (urls_checks.UrlID = urls.UrlID)
WHERE urls.UserID = '1' GROUP BY urls.UrlID

 

and produces a table of data

my problem is if u see the last part of the select

urls_checks.http_code as Last_Code , urls_check.stotal_time as Last_Time

I want this to select the Latest row for that UrlID How can I do this  (I tried adding a where MAX(urls_checks.Date)  but of course that will fail if another check occurs from a different user and return that response code or will it cause of my join???

 

[Barand]

try

SELECT urls.UrlID, urls.Url, urls.Active,
(SELECT COUNT(*) FROM urls_checks WHERE UrlID = urls.UrlID) as Check_count,
X.Last_Check , urls_checks.http_code as Last_Code , urls_checks.total_time as Last_Time
FROM urls
JOIN urls_checks ON (urls_checks.UrlID = urls.UrlID)
JOIN (SELECT UrlID, MAX(Date) as Last_Check FROM urls_checks GROUP BY UrlID) as X
ON urls_checks.UrlID = X.UrlID AND urls_checks.Date = X.Last_Check
WHERE urls.UserID = '1'

[cooldude832]

I gotta say that is one amazing query?!

 

How does it work exactly?

 

I'm assuming the X is a pseudo table that basically contains the latest row for each UrlID from the Urls_checks table?

 

Is that about right???

[Barand]

That's right. Think of X as a separate table containing the urlID and maxdate so we can join to it to get the data from the matching urls_checks record.

 

If you just do a GROUP BY you get the details from the first record in the group.

 

Because its not doing a GROUP BY we do a sepatate subquery to get the COUNT

[lampstax]

Barand.  That IS a bad ass query man.  If you can just see that stuff in your head, you're pretty scary.

 

Mind if I ask what your 9-5 is ?

[cooldude832]

I found a flaw

 

For rows that have nothing in the Urls_checks table they are not returned

 

So i just added a Ping that url once to my adding urls class and its all good, but still like it to have a 0 ping count still be in the query any thoughts?

[Barand]

try

SELECT urls.UrlID, urls.Url, urls.Active,
(SELECT COUNT(*) FROM urls_checks WHERE UrlID = urls.UrlID) as Check_count,
Y.Last_Check , Y.http_code as Last_Code , Y.total_time as Last_Time
FROM urls
LEFT JOIN (SELECT X.Last_Check, urls_checks.UrlID, urls_checks.http_code, urls_checks.total_time
FROM urls_checks
JOIN (SELECT UrlID, MAX(Date) as Last_Check FROM urls_checks GROUP BY UrlID) as X
ON urls_checks.UrlID = X.UrlID AND urls_checks.Date = X.Last_Check) as Y
ON urls.UrlID = Y.UrlID
WHERE urls.UserID = '1'

 

@lampstax,

IT Professional for last 40 years but only the last 20 or so with databases and SQL