Page 1 of 1

Select Distinct Help

Posted: Wed Aug 05, 2009 11:41 am
by Judgement
Ok well I just started coding my own PHP/MySQL stuff a few days ago, so bear with me.

I have a table with fields name, time overallxp, overalllvl, overallrank, and so on.

It updates each day at the same time, inserting a new row for each name I give it, so each person has between 1 and 100+ rows of data.

I need to select only rows with distinct values in the `name` field, but I want all the information from those rows, currently I just get their names.

How do I select only rows with distinct `name` but return all the data in that row?

If you need clarification, just ask.

Re: Select Distinct Help

Posted: Wed Aug 05, 2009 12:13 pm
by pickle
Do you want multiple rows per name, or just the latest information for each distinct name?

Re: Select Distinct Help

Posted: Wed Aug 05, 2009 12:45 pm
by Judgement
For now I just want the latest row for each particular name, eventually I'll need to get rows that are 1 month/1 week/1 day behind that but I'll start with just getting the latest row.

Re: Select Distinct Help

Posted: Wed Aug 05, 2009 1:01 pm
by pickle
Untested, but this might work,

Code: Select all

SELECT
  *
FROM
  `table`
ORDER BY
  `time`
GROUP BY
  `name`
I'm not sure which clause must come first - ORDER BY or GROUP BY. Also, that query assumes `time` records when the record was entered.

Re: Select Distinct Help

Posted: Wed Aug 05, 2009 1:18 pm
by Judgement
Nope :(
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

Re: Select Distinct Help

Posted: Wed Aug 05, 2009 2:12 pm
by pickle
Put in some error reporting so you can see what the problem is. The query's not correct somehow. Or you could run it directly on the command line.

Re: Select Distinct Help

Posted: Wed Aug 05, 2009 2:35 pm
by Judgement
Alright, I figured it out, thanks.