Select Distinct Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Judgement
Forum Newbie
Posts: 5
Joined: Wed Aug 05, 2009 11:37 am

Select Distinct Help

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Select Distinct Help

Post by pickle »

Do you want multiple rows per name, or just the latest information for each distinct name?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Judgement
Forum Newbie
Posts: 5
Joined: Wed Aug 05, 2009 11:37 am

Re: Select Distinct Help

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Select Distinct Help

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Judgement
Forum Newbie
Posts: 5
Joined: Wed Aug 05, 2009 11:37 am

Re: Select Distinct Help

Post by Judgement »

Nope :(
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Select Distinct Help

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Judgement
Forum Newbie
Posts: 5
Joined: Wed Aug 05, 2009 11:37 am

Re: Select Distinct Help

Post by Judgement »

Alright, I figured it out, thanks.
Post Reply