difficult mysql query depending on result of former query - possible?

I'm pretty much a noob and tried to solve this myself and by searching, but I failed big time.

I have a MySQL table like this:

Paul -> 2011-04-01 -> 100

Steven -> 2011-03-01 -> 100

Bob -> 2011-04-01 -> 150

Paul -> 2011-04-02 -> 150

Steven -> 2011-03-02 -> 200

Bob -> 2011-04-01 -> 150

Paul -> 2011-04-03 -> 200

Steven -> 2011-03-03 -> 300

So basically I have a number of names with dates and a score number. The dates might have gaps in between, meaning while Paul might have a value for yesterday, Steven might not. I would like to perform a query in which I find all names of people that have not gained any points within a certain amount of days backwards from their last entry.

I started with something like:

$query = ("SELECT name, MAX(id) FROM ranking GROUP BY name"):

which would e开发者_如何转开发nsure I have the last actual score value for each person, but within this query I need to perform a query that now finds the persons that have a score value for X or more than X days from their latest entry that equals or is greater than the latest score value.

I hope this is somehow understandable.

Before I did all this by querying the whole table and doing the comparisons in PHP. But the table now has almost 10.000 entries which create an array exceeding the memory limit.

Is it possible to make such a specific query or does anybody know another solution for the problem? Thanks in advance for any help.

@mdma Hi again,

Your assumptions are right. Each row holds the score for a name on a certain date. Scores can increase, decrease or stay the same. Since I only add the scores for the 500 people with the highest score somebody might have a value for April 1, but not for April 2 if he drops out of the first 500. He might have one again for April 10, when he's back among the first 500 highest scores.

I tried to implement your query the following way: (I replaced score with points since that's how the field is called)

$query = "SELECT ranking.name, MAX(ranking.id) rankingId, MAX(ranking.points) rankingPoints, MAX(ranking.date) rankingDate, outrank.name, MAX(outranking.id) outrankId, MAX(outrank.points) outrankPoints, MAX(outrank.date) outrankDate " .
    "FROM ranking " .
         "LEFT JOIN " .
              "(SELECT name, id, points, date FROM ranking  GROUP BY name) AS outrank " .
         "ON " .
              "(ranking.name!=outrank.name " .
              "AND outrank.date<=DATE_SUB(ranking.date, INTERVAL 3 DAY) " .
              "AND outrank.points>=ranking.points) " .
         "GROUP BY ranking.name, outrank.name";
$result = mysql_query($query) or die("You wish, sweetheart!");

But it doesn't work so far, the die function gets called. I spent a couple of hours trying to understand the query in detail, yet I did not.^^ I understand the concept of joining two tables, but I don't understand what MAX(ranking.points) rankingPoints does for example. I'm sorry if that comes across a little stupid but I really wrote my first echo "Hello"; 4 weeks ago.

Thank you again!

Hi once more

I kind of made my own work around now. I will query the latest stats for each person with

$query = "SELECT rank.id, rank.name, rank.points, rank.niveau, rank.date

FROM (SELECT name, MAX(date) AS maxdate FROM ranking GROUP BY name) AS x

INNER JOIN ranking AS rank ON rank.name = x.name AND rank.date = x.maxdate

WHERE niveau != '---' ORDER BY name";

Afterwards I make a second query which gets me all result with a date prior X days.

$query = sprintf("SELECT rank.name, rank.date, rank.points, rank.id

FROM (SELECT name, MAX(date) AS maxdate FROM ranking GROUP BY name) AS x

INNER JOIN ranking AS rank ON rank.name = x.name AND rank.date <= date_sub(x.maxdate, INTERVAL '%s' DAY)

WHERE niveau != '---' ORDER BY name, date DESC")

But the second query will give me all rows for a person prior to that date. Is there a way to limit it to just one row per ranking.name?

Thanks!

---

better write a function that gets you gap between last date and date prior to last date like this

CREATE FUNCTION GET_GAP (name VARCHAR)
  RETURNS INT
   DETERMINISTIC
    BEGIN
     DECLARE id,gap INT;
     DECLARE d DATE;
     select max(id) as id, max(date) as date into id,d from A where A.name = name;
     select max(date)-d into gap from A where A.id != id and A.name = name;
     RETURN gap;
    END|

---

To paraphrase, given each person's latest score, find all other persons who had a score greater or equal to that at X or more days ago.

I'm assuming that the ranking is "current running" or cumulative so that each row shows the current score for that person on a given date. The consequence of this is that both scores and dates for a user remain the same or increase with ID. That's my assumption, let me know if it's not correct.

SELECT ranking.name, MAX(ranking.id) rankingId, MAX(ranking.score) rankingScore, MAX(ranking.date) rankingDate, outrank.name, MAX(outranking.id) outrankId, MAX(outrank.score) outrankScore, MAX(outrank.date) outrankDate 
FROM ranking 
   LEFT JOIN 
      (SELECT name, id, score, date FROM ranking  GROUP BY name) AS outrank 
   ON 
      (ranking.name!=outrank.name 
       AND outrank.date<=DATE_SUB(ranking.date, INTERVAL X DAY) 
       AND outrank.score>=ranking.score)
GROUP BY ranking.name, outrank.name

(Replace X in INTERVAL X DAY with the number of days before the latest entry to query.)

The outrank derived table lists those rows that outrank the current row X or more days earlier. By joining this with the latest ranking for each user, we see which other users had a equal or higher score X or more days before. When there are no entries that outrank the current entry, the outrank.* fields will be NULL.