Sort by dates GROUP BY

Navigation
0

Good afternoon.

I'm trying to sort a grouped result and I want to get the last move made by a client with a given ID.

The idea is to group by ID and show only the last query of each client (ID). Doing a test with 6 records of the same client (the actual query is with thousands of clients), there is no way to show me the last query of each one, it always shows me the first of each one , in this case the one dated 2016-12-31 15:27:28.

The field I order is a date type DATE_TIME.

The sentences are the following, where I get the same result in the 4 variants of GROUP BY.

Can any enlightened person give me a hand? 

mysql> SELECT fecha,cliente,dni FROM base.tabla ORDER BY fecha ASC;
+---------------------+---------+-----------+
| fecha               | cliente | dni       |
+---------------------+---------+-----------+
| 2016-12-31 15:27:28 |   17553 | 77889990A |
| 2017-01-01 19:09:53 |   17553 | 77889990A |
| 2017-01-01 19:36:44 |   17553 | 77889990A |
| 2017-01-11 01:35:35 |   17553 | 77889990A |
| 2017-08-27 09:51:38 |   17553 | 77889990A |
| 2017-08-31 13:07:34 |   17553 | 77889990A |
+---------------------+---------+-----------+
6 rows in set (0,00 sec)

mysql> SELECT fecha,cliente,dni FROM (SELECT fecha,cliente,dni FROM base.tabla ORDER BY fecha ASC) as ultimos GROUP BY dni;
+---------------------+---------+-----------+
| fecha               | cliente | dni       |
+---------------------+---------+-----------+
| 2016-12-31 15:27:28 |   17553 | 77889990A |
+---------------------+---------+-----------+
1 row in set (0,00 sec)

mysql> SELECT fecha,cliente,dni FROM (SELECT fecha,cliente,dni FROM base.tabla ORDER BY fecha ASC) as ultimos GROUP BY dni ORDER BY ultimos.fecha ASC;
+---------------------+---------+-----------+
| fecha               | cliente | dni       |
+---------------------+---------+-----------+
| 2016-12-31 15:27:28 |   17553 | 77889990A |
+---------------------+---------+-----------+
1 row in set (0,00 sec)

mysql> SELECT fecha,cliente,dni FROM (SELECT fecha,cliente,dni FROM base.tabla ORDER BY fecha DESC) as ultimos GROUP BY dni ORDER BY ultimos.fecha DESC;
+---------------------+---------+-----------+
| fecha               | cliente | dni       |
+---------------------+---------+-----------+
| 2016-12-31 15:27:28 |   17553 | 77889990A |
+---------------------+---------+-----------+
1 row in set (0,00 sec)

mysql> SELECT fecha,cliente,dni FROM (SELECT fecha,cliente,dni FROM base.tabla ORDER BY fecha ASC) as ultimos GROUP BY dni ORDER BY ultimos.fecha DESC;
+---------------------+---------+-----------+
| fecha               | cliente | dni       |
+---------------------+---------+-----------+
| 2016-12-31 15:27:28 |   17553 | 77889990A |
+---------------------+---------+-----------+
1 row in set (0,00 sec)

mysql> SELECT fecha,cliente,dni FROM (SELECT fecha,cliente,dni FROM base.tabla ORDER BY fecha DESC) as ultimos GROUP BY dni ORDER BY ultimos.fecha ASC;
+---------------------+---------+-----------+
| fecha               | cliente | dni       |
+---------------------+---------+-----------+
| 2016-12-31 15:27:28 |   17553 | 77889990A |
+---------------------+---------+-----------+
1 row in set (0,00 sec)

Thanks in advance.

Greetings

SOLUTION: SELECT MAX (date) AS date, client AS client, dni FROM base.table GROUP BY dni ORDER BY date ASC;

It is not necessary the subquery and therefore not the ORDER BY of the same since the MAX obtains the greater result.

++ IMPORTANT ++

The problem is in the version of MYSQL 5.7.18, with the previous versions there are no problems, the subquery orders by what you request.

Tested in versions 5.1.60, 5.5.40 and 5.6.37. #

    
asked by Jorge Santana 08.09.2017 в 14:39
source

3 answers

1

Maybe that's how it works for you: 

Select cliente, dni, MAX(CAST(fecha AS CHAR))
    from base.Tabla
    group by cliente, dni
    
answered by 08.09.2017 / 14:52
source
0

What these queries do is first group and then sort. Therefore, you are sorting on a single remaining record.

Try to make a group by after the first selection. 

SELECT * 
FROM (
    SELECT fecha,cliente,dni 
    FROM base.tabla 
    ORDER BY fecha ASC
) AS sub
GROUP BY dni

Source

    
answered by 08.09.2017 в 14:48
0

If, as you say in the question, you want a single row, you do not need to use GROUP BY , which is used to group. Another thing would be if you need data from several rows in a single row.

To obtain the last registration, simply:

  • apply a WHERE to differentiate
  • put a descending order
  • limit the result to a record

Something like this: 

SELECT fecha,cliente,dni 
FROM base.tabla 
WHERE dni='77889990A' 
ORDER BY fecha DESC 
LIMIT 1;

Or: 

SELECT fecha,cliente,dni 
FROM base.tabla 
WHERE cliente=17553 
ORDER BY fecha DESC 
LIMIT 1;
    
answered by 08.09.2017 в 15:09
Background that occupies the width and height of the screen Error executing script using BLOB SQL