Table ALIAS of INNER JOINs ?!?!

hey volks.


i'm struggeling really hard on this one! basically i just want to get
the groupwise maximum (in this case the latest entries /
ranks.checked_at). i try something like this:

SELECT urls.url, keywords.keyword, ranks.rank
FROM (urls INNER JOIN keywords ON urls.ID = keywords.url_id) INNER
JOIN ranks ON keywords.ID = ranks.keyword_id AS xxx,
(SELECT keywords.keyword, MAX(ranks.checked_at) AS latest FROM
keywords INNER JOIN ranks ON keywords.ID = ranks.keyword_id GROUP BY
keywords.keyword) AS yyy
WHERE yyy.keyword = xxx.keyword
AND xxx.created_at = yyy.latest;

but it seems as if the 'AS xxx' statement isn't valid after INNER
JOINs of tables...


please help me, i'm really lost!

thanks so much,
-bernd

argh! had a typo. forget about it...

I am in the learning stage for subqueries but I see several errors.

Your FROM has a comma -- xxx, (SELECT -- and that is not allowed.

Your AS xxx is misplaced. Needs to be -- INNER JOIN ranks AS xxx ON
keywords.ID = xxx.keyword_id

I can not figure what you want from the subquery. You have it in the FROM
clause but also have output of subquery 'AS yyy' appear as if you want an
output field.

Explain a little more what you are trying to do with the subquery.

--
Build a little, test a little.


"snufa snafu" wrote:

hey volks.


i'm struggeling really hard on this one! basically i just want to get
the groupwise maximum (in this case the latest entries /
ranks.checked_at). i try something like this:

SELECT urls.url, keywords.keyword, ranks.rank
FROM (urls INNER JOIN keywords ON urls.ID = keywords.url_id) INNER
JOIN ranks ON keywords.ID = ranks.keyword_id AS xxx,
(SELECT keywords.keyword, MAX(ranks.checked_at) AS latest FROM
keywords INNER JOIN ranks ON keywords.ID = ranks.keyword_id GROUP BY
keywords.keyword) AS yyy
WHERE yyy.keyword = xxx.keyword
AND xxx.created_at = yyy.latest;

but it seems as if the 'AS xxx' statement isn't valid after INNER
JOINs of tables...


please help me, i'm really lost!

thanks so much,
-bernd
.