CODEWITHSUNDEEP
mysqlsql
murgatroid99
murgatroid99

Reputation: 20297

Get rows with second-most-recent timestamp by category?

I have a MySQL table (called formstatus) with 4 columns: id, form, status, and timestamp, with id as the primary key and form and status unique together. I would like to get the set of rows (the status in particular) with the second-most-recent timestamp per form. I tried

SELECT form, status FROM (
  SELECT form, status, timestamp
  FROM formstatus
  WHERE timestamp < max(timestamp)
  GROUP BY form) as a
WHERE timestamp = max(timestamp)
GROUP BY form

but it didn't work because apparently aggregates are not allowed in where clauses. So, is there a query that will return the information I am looking for?

Upvotes: 0

Views: 1703

Answers (2)

Devart
Devart

Reputation: 122002

Try this query -

SELECT
  t1.*, COUNT(*) pos
FROM formstatus t1
  LEFT JOIN formstatus t2
    ON t2.form = t1.form AND t2.timestamp >= t1.timestamp
GROUP BY
  t1.form, t1.timestamp
HAVING
  pos = 2;

Upvotes: 3

mellamokb
mellamokb

Reputation: 56779

The error is generated because you cannot use aggregates in a WHERE clause. Try moving your condition to a HAVING clause.

GROUP BY form
HAVING timestamp < max(timestamp)

Upvotes: 0

Related Questions