SQL statement to find a last record per customer by date

Question

SQL statement to find a last record per customer by date

Navigation

#1 by (4 votes)
#2 by (2 votes)

3

I am trying to create a statement that returns a field from a date, but under a certain condition.

First of all I have a table with several fields, among which I am interested in the following image:

What I need is for you to return the last record entered by date.

Ex: Search criteria 2018-02-28 Returns the CLIENT ID 4 .

Ex: Search criteria 2018-03-04 Returns the CLIENT ID 2 .

Ex: Search criteria 2018-02-15 does not return anything.

So far I have the following code:

SELECT 'cliente' FROM 'Historial' WHERE 'fecha' LIKE '%$busqueda%' GROUP BY 'cliente' ORDER BY 'fecha' DESC

Where $ search comes to be the date.

I would appreciate any help. Thank you very much.

mysql sql php

asked by argven tecnology 05.03.2018 в 15:55

source

2 answers

2

Knowing that you have an additional field that is ID and is AUTOINCREMENT , you could use the following query:

SELECT cliente, fecha FROM historial WHERE fecha ='$busqueda'
ORDER BY ID DESC LIMIT 1

answered by 05.03.2018 в 18:39

Show value of a struct How can I print the student with the highest grade and the student with the lowest grade of this JSON?

score 4 · Accepted Answer

You can make a MAX of the CUSTOMER ID in the following way:

select 
  max(A.Fecha),A.idcliente
from 
  historial A 
      inner join (SELECT 
                    MAX(id) as id,idcliente 
                FROM 
                    Historial 

                GROUP BY 
                    idcliente ) B on b.idcliente = a.idcliente
group by 
    A.idcliente
having 
  max(A.Fecha)= '2018-02-26'

Remember that if you want to add more fields to the selection of your query you should also add them to the group by, such as the name of the client.

link