Как получить вторую МАКСИМАЛЬНУЮ ДАТУ в MYSQL

Было не интересно читать ваш запрос, но я думаю, что проблема здесь:

 LEFT JOIN ( SELECT max(notification_date) notification_date, client_id FROM og_ratings WHERE notification_date NOT IN ( SELECT max(notification_date) FROM og_ratings ) 

если вы хотите, чтобы максимальная дата для каждого клиента вам нужна для GROUP BY client_id:

 SELECT client_id, max(notification_date) notification_date FROM og_ratings GROUP BY client_id 

если вам нужен второй максимум, есть несколько вариантов, я использую этот, который легче понять, но это не обязательно самый результативный:

 SELECT client_id, max(notification_date) notification_date FROM og_ratings WHERE (client_id, notification_date) NOT IN ( SELECT client_id, max(notification_date) FROM og_ratings GROUP BY client_id ) GROUP BY client_id 

третья проблема, вы используете LEFT JOIN, что означает, что вы вернете все значения из og_ratings независимо от того, являются ли они вторым максимумом или нет. Используйте INNER JOIN в этом контексте:

 SELECT r.client_id, c.id, t.id, ..etc... FROM og_ratings r INNER JOIN ( SELECT client_id, max(notification_date) notification_2nd_date FROM og_ratings WHERE (client_id, notification_date) NOT IN ( SELECT client_id, max(notification_date) FROM og_ratings GROUP BY client_id ) GROUP BY client_id ) r2 ON r.notification_date = r2.notification_2nd_date AND r.client_id = r2.client_id LEFT JOIN og_companies c ON r.client_id = c.id LEFT JOIN og_rating_types t ON r.rating_type_id = t.id LEFT JOIN og_actions a ON r.pacra_action = a.id LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id LEFT JOIN og_lterms l ON r.pacra_lterm = l.id LEFT JOIN og_sterms s ON r.pacra_sterm = s.id LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id LEFT JOIN pacra_clients pc ON pc.id = pr.client_id LEFT JOIN city ON city.id = pc.head_office_id WHERE r.client_id IN ( SELECT opinion_id FROM pacra_client_opinion_relations WHERE client_id = 50 ) 

Обратите внимание, что настройки схемы, QueryA и QueryB предназначены только для визуализации.

В то время как QueryC – это тот, который вы можете попробовать с вашими данными.

Причина, по которой это не делает простой order и limit что @Musa делает просто: у вас может быть много строк со второй наибольшей датой, а не с одной. Именно поэтому для целей второй группы используются переменные @grp и @prevdate.

Настройка схемы

 -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); от -- drop table specimenA; create table specimenA ( mypk int auto_increment primary key, id int not null, -- note, not autoinc or pk theDate date not null, title varchar(255) not null, otherThing varchar(20) not null -- etc -- not other indexes whatsoever ); -- truncate table specimenA insert specimenA (id,theDate,title,otherThing) values (170,'2007-09-19','whatever','whatever'), (170,'2008-09-12','whatever','whatever'), (170,'2010-01-15','whatever','whatever'), (170,'2011-02-03','whatever','whatever'), (170,'2012-06-26','whatever','whatever'), (170,'2013-03-05','whatever','whatever'), (170,'2014-06-25','whatever','whatever'), (170,'2015-06-09','whatever','whatever'), (917,'2009-10-14','whatever','whatever'), (917,'2008-12-31','whatever','whatever'), (109,'2010-04-26','whatever','whatever'), (109,'2011-03-02','whatever','whatever'), (109,'2012-06-25','whatever','whatever'), (109,'2013-01-04','whatever','whatever'), (109,'2014-03-28','whatever','whatever'), (109,'2015-03-18','whatever','whatever'), (1057,'2014-03-28','whatever','whatever'), (1057,'2014-11-21','whatever','whatever'), (1057,'2015-08-13','whatever','whatever'); 

QueryA

 set @rn:=0,@grp:=0,@prevdate:=''; select id,theDate,title,otherThing, @rn:=@rn+1 as rownum, @grp:=if(@prevdate=theDate,@grp,@grp+1) as descGrp, @prevdate:=theDate as unused from specimenA order by theDate DESC -- **** Note this -- DESC means greatest first, as is most-recent first for dates +------+------------+----------+------------+--------+---------+------------+ | id | theDate | title | otherThing | rownum | descGrp | unused | +------+------------+----------+------------+--------+---------+------------+ | 1057 | 2015-08-13 | whatever | whatever | 1 | 1 | 2015-08-13 | | 170 | 2015-06-09 | whatever | whatever | 2 | 2 | 2015-06-09 | | 109 | 2015-03-18 | whatever | whatever | 3 | 3 | 2015-03-18 | | 1057 | 2014-11-21 | whatever | whatever | 4 | 4 | 2014-11-21 | | 170 | 2014-06-25 | whatever | whatever | 5 | 5 | 2014-06-25 | | 1057 | 2014-03-28 | whatever | whatever | 6 | 6 | 2014-03-28 | | 109 | 2014-03-28 | whatever | whatever | 7 | 6 | 2014-03-28 | | 170 | 2013-03-05 | whatever | whatever | 8 | 7 | 2013-03-05 | | 109 | 2013-01-04 | whatever | whatever | 9 | 8 | 2013-01-04 | | 170 | 2012-06-26 | whatever | whatever | 10 | 9 | 2012-06-26 | | 109 | 2012-06-25 | whatever | whatever | 11 | 10 | 2012-06-25 | | 109 | 2011-03-02 | whatever | whatever | 12 | 11 | 2011-03-02 | | 170 | 2011-02-03 | whatever | whatever | 13 | 12 | 2011-02-03 | | 109 | 2010-04-26 | whatever | whatever | 14 | 13 | 2010-04-26 | | 170 | 2010-01-15 | whatever | whatever | 15 | 14 | 2010-01-15 | | 917 | 2009-10-14 | whatever | whatever | 16 | 15 | 2009-10-14 | | 917 | 2008-12-31 | whatever | whatever | 17 | 16 | 2008-12-31 | | 170 | 2008-09-12 | whatever | whatever | 18 | 17 | 2008-09-12 | | 170 | 2007-09-19 | whatever | whatever | 19 | 18 | 2007-09-19 | +------+------------+----------+------------+--------+---------+------------+ 

И, беря вышеуказанный оператор select и делая его производной таблицей как псевдоним inR , вложенные:

QueryB

 set @rn:=0,@grp:=0,@prevdate:=''; select id,theDate,title,otherthing from ( select id,theDate,title,otherThing, @rn:=@rn+1 as rownum, @grp:=if(@prevdate=theDate,@grp,@grp+1) as descGrp, @prevdate:=theDate as unused from specimenA order by theDate DESC -- **** Note this ) inR where descGrp=2; +-----+------------+----------+------------+ | id | theDate | title | otherthing | +-----+------------+----------+------------+ | 170 | 2015-06-09 | whatever | whatever | +-----+------------+----------+------------+ 

И есть вторая по величине дата. Значение второй самой последней даты.

Итак, взяв ваш оригинальный отборный вариант, та же концепция. Мотивация для показа приведенного выше заключается в следующем: InR – это только Derived Table , не более или менее, чем ваш оператор select, который станет производной таблицей.

QueryC

 set @rn:=0,@grp:=0,@prevdate:=''; select client_id, cid, tid, aid, oid, opinion, notification_date, ttitle, atitle, otitle, ltitle, stitle, opinion_id, pcid, pr_client, address, liaison_one, cityid, head_office_id, city, cname from ( SELECT r.client_id as client_id,c.id as cid,t.id as tid,a.id as aid,o.id as oid,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id as pcid, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id as cityid, pc.head_office_id, city.city, pc.title as cname, @rn:=@rn+1 as rownum, @grp:=if(@prevdate=r.notification_date,@grp,@grp+1) as descGrp, @prevdate:=r.notification_date as unused FROM og_ratings r LEFT join ( select max(notification_date) notification_date, client_id from og_ratings WHERE notification_date NOT IN (select max(notification_date) FROM og_ratings ) ) r2 on r.notification_date = r2.notification_date and r.client_id = r2.client_id LEFT JOIN og_companies c ON r.client_id = c.id LEFT JOIN og_rating_types t ON r.rating_type_id = t.id LEFT JOIN og_actions a ON r.pacra_action = a.id LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id LEFT JOIN og_lterms l ON r.pacra_lterm = l.id LEFT JOIN og_sterms s ON r.pacra_sterm = s.id LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id LEFT JOIN pacra_clients pc ON pc.id = pr.client_id LEFT JOIN city ON city.id = pc.head_office_id WHERE r.client_id IN (SELECT opinion_id FROM pacra_client_opinion_relations WHERE client_id = 50) order by r.notification_date DESC ) inR where descGrp=2 

QueryC – это то, с чем вы работаете. Поскольку это два утверждения

• Инициализация переменных
• и большая строка запроса

… вам нужно запустить их в таком порядке с запросом, по одному для каждого, в этом порядке или использовать многопользовательский запрос PHP и объединить оба в одном вызове. Эта ссылка в предыдущем предложении предназначалась для mysqli, здесь раскрывается концепция, соответствующим образом изменяйте.

Вы можете использовать просто под запросом, чтобы получить вторую максимальную дату в таблице или с вашим требованием: –

 select joiningDate from t_member order by joiningDate desc limit 1,1 

Вы можете использовать ORDER BY DATE_COLUMN DESC LIMIT 1 OFFSET 1 . Я добавил этот код в конце запроса:

 SELECT r.client_id, c.id, t.id, a.id, o.id, c.name as opinion, r.notification_date, t.title as ttitle, a.title as atitle, o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname FROM og_ratings r LEFT join ( select max(notification_date) notification_date, client_id from og_ratings WHERE notification_date NOT IN (select max(notification_date) FROM og_ratings ) ) r2 on r.notification_date = r2.notification_date and r.client_id = r2.client_id LEFT JOIN og_companies c ON r.client_id = c.id LEFT JOIN og_rating_types t ON r.rating_type_id = t.id LEFT JOIN og_actions a ON r.pacra_action = a.id LEFT JOIN og_outlooks o ON r.pacra_outlook = o.id LEFT JOIN og_lterms l ON r.pacra_lterm = l.id LEFT JOIN og_sterms s ON r.pacra_sterm = s.id LEFT JOIN pacra_client_opinion_relations pr ON pr.opinion_id = c.id LEFT JOIN pacra_clients pc ON pc.id = pr.client_id LEFT JOIN city ON city.id = pc.head_office_id WHERE r.client_id IN ( SELECT opinion_id FROM pacra_client_opinion_relations WHERE client_id = 50 ) ORDER BY r.notification_date DESC # Add this line LIMIT 1 offset 1 # and this line