Q:

GROUP BY name and ORDER BY point & time MYSQLi

I'm new to this forum and I hope to find my solution about my problem.
I have this table :
name     time  points  car    date
Daniel | 55s | 210   | red   |20/01/2018
Daniel | 45s | 250   | green |21/01/2018
Julie  | 54s | 220   | red   |19/01/2018
Julie  | 33s | 150   | yellow|22/01/2018

and I wish to sort it like this
name     time  points  car    date
Daniel | 45s | 250    |green |21/01/2018
Julie  | 54s | 220    |red   |19/01/2018

first sorting by points, than sorting by time and group by name (optional the count)
I use this
    SELECT NAME, MAX(POINTS) POINTS, MAX(TIME) TIME, MAX(CAR) CAR, MAX(DATE) DATE
 FROM ( SELECT A.* FROM test A LEFT OUTER JOIN test B ON A.NAME=B.NAME AND
 A.POINTS<B.POINTS AND A.TIME>B.TIME WHERE B.NAME IS NULL ) as sub GROUP BY NAME

and I get this :
 name   POINTS  TIME    CAR     DATE    
Daniel  250     45    green     2018-01-21
Julie   220     54    yellow    2018-01-22

Julie should have car=red & 2018-01-19
For Daniel it looks good
how can I get thise values (car & date) ?
thanks
Nico

A:

You could give this a shot.  It contains the table against itself and gets only records with the highest points and lowest time.
SELECT NAME, MAX(POINTS) POINTS, MAX(TIME) TIME
FROM
(
    SELECT A.* FROM test A
        LEFT OUTER JOIN test B ON A.NAME=B.NAME AND A.POINTS<B.POINTS AND A.TIME>B.TIME
    WHERE B.NAME IS NULL
) GROUP BY NAME

For additional, try this:
SELECT * FROM
(
SELECT OUTERTEST.*,
    @row_num := IF(@prev_value=OUTERTEST.name,@row_num+1,1) AS RowNumber,
    @prev_value := OUTERTEST.name
FROM (SELECT * FROM TEST ORDER BY NAME, TEST.POINTS DESC, TEST.TIME ASC) OUTERTEST, (SELECT @row_num := 1, @prev_value := '') x
) A
WHERE A.ROWNUMBER=1

