SELECT rows with the second highest value in a column
database, mysql, select, sql
Solution
You can try something like:
SELECT MAX(Time)
FROM yourTable
WHERE Time < (SELECT MAX(Time) FROM yourTable)
SQLFiddle Demo
Problem
Let's say I have a table similar to the following: ``` Item Description Time ----- ----------- ----- ItemA1 descript 08-16-2013 00:00:00 ItemA2 descript 08-16-2013 00:00:00 ItemA3 descript 08-16-2013 00:00:00 . . ItemAN descript 08-16-2013 00:00:00 ItemB1 descript 08-13-2013 00:00:00 ItemB2 descript 08-13-2013 00:00:00 ItemB3 descript 08-13-2013 00:00:00 . . ItemBN descript 08-13-2013 00:00:00 . . . ItemX1 descript 01-13-2012 00:00:00 ItemX2 descript 01-13-2012 00:00:00 ItemX3 descript 01-13-2012 00:00:00 . . ItemXN descript 01-13-2012 00:00:00 ``` Groups of items are added periodically. When a group of items is added they are all added with the same "Time" field. "Time" essentially serves as a unique index for that item group. I want to SELECT the group of items that have the second highest time. In this example my query should pull the "B" items. I know I can do max(`time`) to SELECT the "A" items, but I don't know how I would do second last. My "Time" columns are stored as TIMESTAMP if that means anything.