John Obertan John Obertan - 1 year ago 67
SQL Question

SELECT rows the the second highest value in a column

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

Answer Source

You can try something like:

FROM yourTable
WHERE Time < (SELECT MAX(Time) FROM yourTable)

SQLFiddle Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download