Second highest value for each id-CodePudding

I have a table with all the appointments information

id_appoint	id_doc	app_date(d/m/y)
17	201	30/10/22
16	202	20/10/22
15	203	19/10/22
14	204	18/10/22
13	201	30/09/22
12	202	20/09/22
11	203	19/08/22
10	204	18/07/22

what I'm trying to get is the second highest date for each doctor's appointment, for example THIS IS THE TABLE I WANT TO CREATE (I don't have this table) (I'm trying to use zoho analytics to introduce this query)

id_appoint	id_doc	app_date
13	201	30/09/22
12	202	20/09/22
11	203	19/08/22
10	204	18/07/22

This doesn't work because it only excludes the highest date of the whole table

SELECT id_doc, MAX( app_date )
  FROM table1
GROUP BY id_doc
 WHERE col < ( SELECT MAX( app_date )
                 FROM table1 )

CodePudding user response：

You need to make the subquery correlated to the main query using a WHERE clause. And GROUP BY goes at the end.

SELECT id_doc, MAX( app_date )
FROM table1 AS t1
WHERE col < (SELECT MAX( app_date )
            FROM table1 AS t2
            WHERE t2.id_doc = t1.id_doc)
GROUP BY id_doc

Or join with the subquery.

SELECT t1.id_doc, MAX(t1.app_date)
FROM table1 AS t1
JOIN (
    SELECT id_doc, MAX(app_date) AS maxdate
    FROM table1
    GROUP BY id_doc
) AS t2 ON t1.id_doc = t2.id_doc AND t1.app_date < t2.app_date
GROUP BY t1.id_doc