Pulling the latest values from a table

[bostonguy70]

I am not a mysql expert and was trying to find something like this.

I have two tables.
Comments Table has 3 fields, country, comments and date with NO unique or primary keys

Table below
_____________________
country comments date
one ddd 10/1/2007
two eee 1/1/2007
three aaa 8/1/2007
one aaa 10/11/2007
two qqq 1/11/2007
three www 1/5/2007
one fff 1/3/2007
two rrr 1/8/2007
three yyy 7/1/2007


User Table has 2 fields, user and country. again no unique or primary fields
User Table Below
_____________
user country
jack one
tom three
jack three

I would like to do this.
Display Just two LATEST comments from the comments table for EACH User.
For example with the above query and tables, i would see something like this as a result, or at least expect it.

jack one ddd 10/1/2007
jack one aaa 10/11/2007
tom three aaa 8/1/2007
tom three yyy 7/1/2007

Any help would be much appreciated. Thank u.

[pbmods]

Heya, bostonguy70. Welcome to TSDN!

With no unique/primary keys, it's pretty tough to do this efficiently.

Looks like something along the lines of:

SELECT
        `users`.`user`,
        `users`.`country`,
        `comments`.`comments`,
        `comments`.`date`
    FROM
    (
            `comments`
        LEFT JOIN
            `users`
                USING (`country`)
    )
    WHERE
    (
        `comments`.`date`
        IN
        (
            SELECT
                    `sub`.`date`
                FROM
                    `comments` AS `sub`
                WHERE
                    `sub`.`country` = `users`.`country`
                ORDER BY
                    `date` DESC
                LIMIT 2
        )
    )
    GROUP BY
        `users`.`country`,
        `comments`.`date`
 

Not sure how you'd want to report multiple Users per country; this query more-or-less randomly truncates results so that you're only fetching two records per country. You can also allow for unique Users by adding it to the GROUP BY clause.