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

user_17727541 0
Viewed 95

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
group-by mysql sql
4 Answers

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.

user_104988280 
SELECT * 
FROM current
WHERE id IN (
  SELECT DISTINCT T.id FROM current AS T
  WHERE id=(
    SELECT id FROM current
    WHERE symbol=T.symbol
    ORDER BY id DESC LIMIT 1,1
  )
)
user_134984370

Easy if your MySql can use ROW_NUMBER. (MySql 8)
Just make it sort descending, then take the 2nd.

WITH CTE AS (
    SELECT *
    , ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY id DESC) AS symbol_rn
    FROM current
) 
SELECT id, symbol
FROM CTE
WHERE symbol_rn = 2
ORDER BY id;

In MySql 7.5 you can simply self-join on the symbol, and group by.
Then the 2nd last will have 1 higher id.

SELECT c1.id, c1.symbol
FROM current c1
LEFT JOIN current c2
  ON c2.symbol = c1.symbol
 AND c2.id >= c1.id
GROUP BY c1.id, c1.symbol
HAVING COUNT(c2.id) = 2
ORDER BY c1.id;
id symbol
5 B
6 A
7 C

db<>fiddle here

The performance will really benefit from an index on symbol.

user_40034190

You can try this;

SELECT * 
FROM current
WHERE id
IN (SELECT MAX(id)
FROM current
GROUP BY symbol)
ORDER BY id DESC LIMIT 1,3

limit 1,3 says; get the last 3 results excluding the last result. You can change the numbers.

user_176870110
Related