If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Table ALIAS of INNER JOINs ?!?!
argh! had a typo. forget about it...
|
#3
|
|||
|
|||
Table ALIAS of INNER JOINs ?!?!
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 . |
Thread Tools | |
Display Modes | |
|
|