MySQL Problem - Distinct column from table.

[diestro]

Hi,

Having some trouble getting it all together.

I have a table in which i need to get Distinct names based on latest date.

This is what the table looks like;

Table Scores;

---------------------------------------------------------------------
|   Name  |     Date   |  High Score  |  Medium Score  |  Low Score  |
 ---------------------------------------------------------------------
| Patrik  | 2011-05-04 |    25        |  28            |  21         |
 --------------------------------------------------------------------
| Patrik  | 2011-05-28 |    30        |  32            |  28         |
 --------------------------------------------------------------------
| Stefan  | 2011-06-28 |    20        |  15            |  10         |
 --------------------------------------------------------------------
| Stefan  | 2011-07-28 |    23        |  19            |  10         |
 --------------------------------------------------------------------

I need to get the latest high Score for each Person based on 'Date'.

So basically, i want the values "Patrik - 30" & "Stefan - 23"

I've tried many different queries but i just can't get it to work.

Thanks

[Buppy]

SELECT *
FROM yourtable
ORDER BY date DESC
GROUP BY name

try this

[diestro]

SELECT *
FROM yourtable
ORDER BY date DESC
GROUP BY name

try this

Hi,

Thanks, but I've tried this allready.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY report_name

Are the latest high scores always the highest for an individual? If so, easy:

SELECT `name`, MAX(`date`), MAX(`high_score`) FROM `table` GROUP BY `name`

otherwise I'd need to think about it.

[diestro]

Are the latest high scores always the highest for an individual? If so, easy:

SELECT `name`, MAX(`date`), MAX(`high_score`) FROM `table` GROUP BY `name`

otherwise I'd need to think about it.

First of, Thanks! :)

But, the problem is that the latest high score isn't always the Highest.
When i run the mentioned query; "SELECT `name`, MAX(`date`), `high_score` FROM `table` GROUP BY `name`"
on the example;

---------------------------------------------------------------------
    | Name    | Date       | High Score | Medium Score | Low Score |
    ---------------------------------------------------------------------
    | Patrik  | 2011-05-04 |  25        | 28           | 21        |
    --------------------------------------------------------------------
    | Patrik  | 2011-05-28 |  30        | 32           | 28        |
    --------------------------------------------------------------------
    | Stefan  | 2011-06-28 |  20        | 15           | 10        |
    --------------------------------------------------------------------
    | Stefan  | 2011-07-28 |  19        | 19           | 10        |
    --------------------------------------------------------------------

it prints;
1: Patrik, 2011-05-28, 30
2: Stefan, 2011-07-28, 20

Cause, I can't sort it by Max(date) as the results vary.

[diestro]

So..

I think I've sorted it out... it took some hard-ass googling ;)

select f.report_name, f.scan_date, f.report_foundstone_score
from (select report_name, max(scan_date) as maxdate from all_reports group by report_name) as x inner join all_reports as f on f.report_name = x.report_name and f.scan_date = x.maxdate

Thanks for all the help i got :)