How to Get the latest and oldest row from group by min/max in mysql

Question

I have an app where I am selecting records with first and the last timestamp for that day. There are more than one rows that satisfies the criteria, I want only the latest for that specific day.

SELECT TIMESTAMP, COUNT(DISTINCT employee_id) 
    FROM attendance 
        WHERE Date(TIMESTAMP) BETWEEN STR_TO_DATE('01/04/2019', '%d/%m/%Y') 
                                  AND STR_TO_DATE('30/04/2019', '%d/%m/%Y') 
          AND TIME(TIMESTAMP) < '17:00' 
          AND employee_id=1111 
    GROUP BY DATE(TIMESTAMP);

The problem is that it doesn't return the last/latest records for those days.

Sample data

could you please provide sample data and your expected output — Fahmi, May 14 '19 at 06:04
What is COUNT(DISTINCT employee_id) supposed to do - is it the employees for each day or employees over the entire period specified in the where clause? Sample data and expected output would help clarify. — P.Salmon, May 14 '19 at 06:47
I don't know how it's duplicate ? I have checked the answer and it doesn't help me in any way. :/ — AdPy, May 15 '19 at 08:49

Shweta · Answer 1 · 2019-05-14T08:26:21.380

1

If you want day wise, oldest and latest entry in particular date range then you can group by date column and order by timestamp column.

Query for fetching latest entry:

SELECT TIMESTAMP, COUNT(DISTINCT employee_id) 
   FROM attendance 
        WHERE Date(TIMESTAMP) BETWEEN STR_TO_DATE('01/04/2019', '%d/%m/%Y') 
          AND STR_TO_DATE('30/04/2019', '%d/%m/%Y') 
          AND TIME(TIMESTAMP) < '17:00' 
          AND employee_id=1111 
        GROUP BY DATE(TIMESTAMP)
        ORDER BY(TIMESTAMP) DESC;

Query for fetching oldest entry:

SELECT TIMESTAMP, COUNT(DISTINCT employee_id) 
    FROM attendance 
        WHERE Date(TIMESTAMP) BETWEEN STR_TO_DATE('01/04/2019', '%d/%m/%Y') 
                                  AND STR_TO_DATE('30/04/2019', '%d/%m/%Y') 
           AND TIME(TIMESTAMP) < '17:00' 
           AND employee_id=1111 
        GROUP BY DATE(TIMESTAMP)
        ORDER BY(TIMESTAMP) ASC;

Please note, I have only used GROUP BY statement and ORDER BY DESC/ASC (based on your requirement for fetching latest/oldest data respectively which matches with the specified criteria).

Hope it works!

edited May 14 '19 at 08:26

answered May 14 '19 at 06:22

Shweta

661
6
11

no, I actually want latest record for each day in the date range. I have used between as you can see. – AdPy May 14 '19 at 06:27
Note that functions (as above) cannot use indexes, so this will be painfully slow on larger data sets. – Strawberry May 14 '19 at 06:58
@AdnanNazir, I have updated the solution by removing LIMIT from Query and adding Group By date column to get day wise oldest and latest data. – Shweta May 14 '19 at 08:27
@GirlHasNoName It still returns values that are not max and also the values are duplicated. – AdPy May 15 '19 at 07:31

How to Get the latest and oldest row from group by min/max in mysql

1 Answers1