Consultation with GROUP BY MYSQL

Navigation
3

I have this table 

  id   departure    value          date 
1065 | 2018-09-20 | 10490 | 2018-09-20 18:04:19 |
1034 | 2018-09-25 | 10582 | 2018-09-20 18:04:19 |
1035 | 2018-09-25 | 13096 | 2018-09-20 19:05:19 |

And this consultation 

SELECT * FROM departures GROUP BY departure

But doing GROUP BY gives this result: 

  id   departure    value          date 
1065 | 2018-09-20 | 10490 | 2018-09-20 18:04:19 |
1034 | 2018-09-25 | 10582 | 2018-09-20 18:04:19 |

I need that when doing GROUP BY bring the most recent record of the departure field according to the date field and not the first one. That is, it should be in value 13096 and not 10582. I can not have more than one record per date in the departure field.

I can not use subqueries.

I appreciate your help.

    
asked by Lina Cortés 21.09.2018 в 16:56
source

5 answers

2

If the id is the pk of the table and you can use it to determine the last row for each group, a simple way could be: 

SELECT  D.id,
        D.departure,
        D.value,
        D.date
    FROM  departure D
    INNER JOIN (SELECT departure, 
                       max(ID) 'MaxId'
            FROM departures 
            GROUP BY departure
        ) M
        ON M.MaxId = D.id
    
answered by 21.09.2018 в 17:34
0

Depending on how I see the exercise, you should not use a GROUP BY , unless you want to use a function like SUM, MAX, MIN , etc. If you want to order a field in descending order, what you should do is use ORDER BY at the end of the query, attaching the [field] plus the word > DESC . In your case: 

SELECT*
FROM departures
ORDER BY departure DESC

EDITING

If you do not have to repeat the departure , use DISTINCT instead of GROUP BY , as follows: 

SELECT DISTINCT departure -- sigues agregando aquí los campos.
FROM departures
ORDER BY departure DESC

Try it and tell us how it went. Greetings.

    
answered by 21.09.2018 в 16:59
0

You could apply the Max (departure) function 

select id,max(departure),value,date 
from departures GROUP BY departure
    
answered by 21.09.2018 в 17:05
0

Try with:

SELECT * FROM departures GROUP BY departure ORDER BY date desc;

(but try with asc ), you tell me, regards.

    
answered by 21.09.2018 в 17:09
0

If what you want is that it brings you the most recent value what you need to do is this, 

SELECT id,departure,value,date
FROM departures
GROUP BY id,departure,value,date 
ORDER BY departure, value  DESC
    
answered by 21.09.2018 в 17:00
Problem with $ _POST, $ _ GET ... with double dollar Obtain the client's IP in Node.js