MySQL Max Date Query – How to Query for Max Date Not Today in MySQL

greatest-n-per-groupMySQL

I have the following table:

Name – Date
Adam – 7-13-19
Stan – 7-13-19
Adam- 7-11-19
Bob – 7-10-19
Stan- 7-10-19
Eric- 7-10-19
Lucy- 7-09-19
Eric- 7-09-19
Bob – 7-09-19
Lucy – 7-08-19

I would like to pull each unique name (which I'm using GROUP BY to do) and the latest corresponding date for each name (which I'm using Max(date) to do). However, I don't want the name to appear if the max(date) is equal to today's date (7-13-19). So the end result I'm looking for is:

Bob
Eric
Lucy

The list would not include Adam or Stan…since they both have max dates of 7-13-19. I've tried a whole bunch of queries, but can't get it to work. My initial thought was something like this:

SELECT *, max(date)
FROM table
WHERE max(date) <> date(now())
GROUP BY name

Any help would be greatly appreciated.

Best Answer

If you want to use aggregate results as a filter, you need to use the HAVING clause.

Something like this should work for you.

SELECT name
    , MAX(date) AS max_date
FROM table 
GROUP BY name
HAVING MAX(date(date)) <> DATE(NOW())

Related Solutions

MySQL schema for recurring events – Query by specific date

Let's say you made the table like this

CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    station_arr_id INT NOT NULL,
    station_dep_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY station_arr_index (station_arr_id,arr_dt),
    KEY station_dep_index (station_dep_id,dep_dt)
);

Next, you insert into the table the event

Arriving From Station 21 on April 30, 2014
Arrived at Station 29 at 10:00 AM on April 30, 2014
Departing From Station 29 1 minute later

Here is that INSERT query

INSERT INTO TrainEvents (station_arr_id,arr_dt,station_dep_id,dep_dt)
VALUES (21,'2014-04-30 10:00:00',29,'2014-04-30 10:01:00');

OK, now let's see how you can query the following departure

All Departures 10:00 AM on April 30, 2014 from Station 29
Check Departures 15 minutes before and after

Here is that SELECT query

SET @MinuteWindow = 15;
SET @DepartureDT = '2014-04-30 10:00:00';
SET @DepartureDTMinBef = @DepartureDT - INTERVAL @MinuteWindow MINUTE;
SET @DepartureDTMinAft = @DepartureDT + INTERVAL @MinuteWindow MINUTE;
SELECT * FROM TrainEvents
WHERE station_dep_id = 29
AND dep_dt >= @DepartureDTMinBef;
AND dep_dt <= @DepartureDTMinAft;

EPILOGUE

Given your initial idea, you could change the station_arr_id and station_dep_id into a single route_id and store the routes in a route table

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY arr_index (route_id,arr_dt),
    KEY dep_index (route_id,dep_dt)
);

You could also make the event record arrivals and departures separately.

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    event_dt DATETIME NOT NULL,
    event_type TINYINT NOT NULL # 1 for arrival, 2 for departure

    ...
    PRIMARY KEY (id),
    KEY event_index1 (route_id,event_dt,event_type),
    KEY event_index2 (route_id,event_type,event_dt),
);

I am just giving ideas. I'll leave it to you to implement. Perhaps, to give you some UI ideas, see the site I use to commute : http://as0.mta.info/mnr/schedules/sched_form.cfm . The result of the route you pick in that site shows route departures within a 5-hour window (2.5 hours before and 2.5 hours after).

Give it a Try !!!

Mysql – Reporting query blocks other query, but ISOLATION LEVEL READ UNCOMMITTED SET

Rather than address the question as asked, I will address some optimizations.

Don't mix charsets -- I see latin1 and utf8. Charset conversion can prevent usage of indexes.
Smaller leads to faster...
Shrink datatypes where practical -- do you really need BIGINT (8 bytes)? The "operators" could probably do with TINYINT UNSIGNED (1 byte). Etc.
decimal(20,4) -- nice commission! Up to 9999999999999999.9999; I can't think of any currency where that would not be bigger than all the money in the world. And it takes 10 bytes.

I presume you are augmenting, not rebuilding, the "Summary tables"? More discussion here. Proper use of Summary tables is the main way to get performance from "reports".

Time each statement -- perhaps one of them (the UPDATE with the subqueries?) is taking most of the time.