GameSection show random games from each category at portal?

Is it possible to show random games from each category at the portal?

hi Play - it probably is, but a query with a random sort to your games table would be a bit heavy on your front page. Really it should be cached. In addition, I have not yet installed 1.6.4 either here or on test so I won't be making a plugin until I do.

I am waiting to see what bugs there are in 1.6.4 and what effect there may be on my modifications before I install.

Sure. Then please let me know when you could do it
It would be nice

Ok, I have been doing some research on this - so I am gonna stash this code sample here. This will be how we do the query:

Spoiler (Click to View)

Given this data set:

ID Name City Birthyear
1 Egon Spengler New York 1957
2 Mac Taylor New York 1955
3 Sarah Connor Los Angeles 1959
4 Jean-Luc Picard La Barre 2305
5 Ellen Ripley Nostromo 2092
6 James T. Kirk Riverside 2233
7 Henry Jones Chicago 1899

I need to find the 3 oldest persons, but only one of every city.

If it would just be the three oldest, it would be...

Henry Jones / Chicago
Mac Taylor / New York
Egon Spengler / New York

However since both Egon Spengler and Mac Taylor are located in New York, Egon Spengler would drop out and the next one (Sarah Connor / Los Angeles) would come in instead.

Any elegant solutions?

Update:

Currently a variation of PConroy is the best/fastest solution:

Code:

SELECT P.*, COUNT(*) AS ct
   FROM people P
   JOIN (SELECT MIN(Birthyear) AS Birthyear
              FROM people
              GROUP by City) P2 ON P2.Birthyear = P.Birthyear
   GROUP BY P.City
   ORDER BY P.Birthyear ASC
   LIMIT 10;

Ok, I tried this but I keep getting an error - it seems to hate the select join. Its also a performance killer - leefish is running on a very high-powered VPS and it slowed to a crawl - nested select, group by, order by RAND...urk.

What it probably needs is an index creating on the games section tables for CATEGORY and GAME (cid and gid).

leefish;7818 Wrote:Ok, I have been doing some research on this - so I am gonna stash this code sample here. This will be how we do the query:

Spoiler (Click to View)

Given this data set:

ID Name City Birthyear
1 Egon Spengler New York 1957
2 Mac Taylor New York 1955
3 Sarah Connor Los Angeles 1959
4 Jean-Luc Picard La Barre 2305
5 Ellen Ripley Nostromo 2092
6 James T. Kirk Riverside 2233
7 Henry Jones Chicago 1899

I need to find the 3 oldest persons, but only one of every city.

If it would just be the three oldest, it would be...

Henry Jones / Chicago
Mac Taylor / New York
Egon Spengler / New York

However since both Egon Spengler and Mac Taylor are located in New York, Egon Spengler would drop out and the next one (Sarah Connor / Los Angeles) would come in instead.

Any elegant solutions?

Update:

Currently a variation of PConroy is the best/fastest solution:

Code:

SELECT P.*, COUNT(*) AS ct
   FROM people P
   JOIN (SELECT MIN(Birthyear) AS Birthyear
              FROM people
              GROUP by City) P2 ON P2.Birthyear = P.Birthyear
   GROUP BY P.City
   ORDER BY P.Birthyear ASC
   LIMIT 10;

Ok, I tried this but I keep getting an error - it seems to hate the select join. Its also a performance killer - leefish is running on a very high-powered VPS and it slowed to a crawl - nested select, group by, order by RAND...urk.

What it probably needs is an index creating on the games section tables for CATEGORY and GAME (cid and gid).

Okay. It sounds understandable. Would you be able to do it?

Well, I am busy with a couple of other things at the moment - sims related - sims is what I do as well as php.

I haven't forgotten the request though.

That is cool. Take the time you need