[RESOLVED] Without subqueries: distinct people_id, max date?

leatherback

Hi all,

I am building a sports ladder for our internal competition. In it I want of course an overview of the status of members, and the last date they played a game. However, the club where we play has asked for a copy of the competition ladder when I am done. So I would like to keep things fairly simply, requirements wise. I am building based on MySQL 3.2 allowed queries.

This being the case..

=> without using subqueries how would I retrieve from a table the last date that each player played a game?

This is the base-query:

"select distinct username, DATE_FORMAT(ga_date, '%e %M %Y') as game_date 
  from ".$games_table."
            left join $users_table as challenger 
	    on ga_challenger = challenger.pe_id";

=> Is the only way through a second set of queries, which would retrieve for each player, one at a time, the maximum date? Or am I missing an option (Which I hope!)?

Cheers,

Jelle.

Piranha

SELECT u.username, DATE_FORMAT(g.ga_date, '%e %M %Y') as game_date
FROM users u
LEFT JOIN games_table g ON u.ga_challenger = g.pe_id
GROUP BY u.id
ORDER BY g.ga_date

This is untested but should work. It is possible that I have written group by and order by in the wrong order, if it doesn't work just change it. This query shows users that have never played a game as well, if you don't want that change the join to a INNER JOIN.

leatherback

Hi Pirana,

Thx for your reply.

Although I can with some modification get the query to spit out results, this does not allow me to do anything with the date:

this:

SELECT u.pe_id, DATE_FORMAT(g.ga_date, '%e %M %Y') as game_date
FROM people u
LEFT JOIN games g ON g.ga_challenger = u.pe_id
GROUP BY u.pe_id
ORDER BY g.ga_date DESC

and this

SELECT u.pe_id, DATE_FORMAT(g.ga_date, '%e %M %Y') as game_date
FROM people u
LEFT JOIN games g ON g.ga_challenger = u.pe_id
GROUP BY u.pe_id
ORDER BY g.ga_date ASC

Give the same date, which makes me feel that it is a random subset, when group by is used.

I am off to bed for now (1am again here..). I'll have a look again in the morning. If you have any more suggestions, please let me know.

Cheers mate

Piranha

Stupid database that is doing what it should and not what I want it to do 😉

I am going to bed as well, it's 2 AM here. I will test it tomorrow and (hopefully) post a solution.

leatherback

Cheers,

Sleep well!

-- I think I saw weed post a solution to a similar problem once, but with the amount of post of that guys.. Couldn't find it.

Sxooter

Something like :

select username, max(date) from joinedtables group by username

doesn't work?

leatherback

Sxooter wrote:
Something like :

select username, max(date) from joinedtables group by username

doesn't work?

:quiet:
Hehe.. As usual.. Something simple and predictable..

Sxooter: Cheers! Exactly what I was looking for.

Piranha

As usual you are right Sxooter. This worked here:

SELECT p.name, MAX(g.ga_date)
FROM person p
INNER JOIN games g
ON g.personid = p.id
GROUP BY p.id

At least it works on MySQL 4.1.9, don't know if it works on MySQL 3 but it should.