Search code examples
mysqlsqlsql-serversybase

SQL - How do I select a "Next Max" record

Funny one this, I've got a table, "addresses", with a list of address details, some with missing fields. I want to identify these rows, and replace them with the previous address row, however these must only be accounts that are NOT the most recent address on the account, they must be previous addresses. Each address has a sequence number (1,2,3,4 etc), so i cab easily identify the MAX address and make that it's not the most recent address on the account, however how do I then scan for what is effectively, "Max -1", or "one less than max"? Any help would be hugely appreciated.

Solution

  • Try this:

    SELECT MAX(field) FROM table WHERE field < (SELECT MAX(field) FROM table)

    By the way: Here is a good article, which describes how to achieve nth row.