Structure is:

CREATE TABLE current
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(id),
   symbol VARCHAR(5),
   UNIQUE (id), INDEX (symbol)
) ENGINE MyISAM;

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.

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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;

db<>fiddle here

The performance will really benefit from an index on symbol.

CodePudding user response：

@forpas had the best answer.

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

Also, reverting back to InnoDB sped things up a bit.

CodePudding user response：

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.