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 |
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
)
)
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.
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.
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.