How can I select the second-to-last rows in a mysql table, grouped by column?

0

Issue

Structure is:

CREATE TABLE current
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(id),
   symbol VARCHAR(5),
   UNIQUE (id), INDEX (symbol)
) ENGINE MyISAM;
id symbol
1 A
2 B
3 C
4 C
5 B
6 A
7 C
8 C
9 A
10 B

I am using the following

SELECT * 
FROM current
WHERE id
IN 
(
    SELECT MAX(id)
    FROM current
    GROUP BY symbol
)

to return the last records in a table.

id symbol
8 C
9 A
10 B

How can I return the next-to-last results in a similar fashion?

I know that I need

ORDER BY id DESC LIMIT 1,1

somewhere, but my foo is weak.

I would want to return

id symbol
5 B
6 A
7 C

Solution

For versions of MySql prior to 8.0, use a subquery in the WHERE clause to filter out the max id of each symbol and then aggregate:

SELECT MAX(id) id, symbol
FROM current
WHERE id NOT IN (SELECT MAX(id) FROM current GROUP BY symbol)
GROUP BY symbol
ORDER BY id;

See the demo.

Answered By – forpas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More