CODEWITHSUNDEEP
mysqlsqlsql-serversybase
Ben Mummery
Ben Mummery

Reputation: 41

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.

Upvotes: 1

Views: 6489

Answers (3)

J Sidhu
J Sidhu

Reputation: 677

This returns 1st or nth max record.

;WITH Distincts as (
    SELECT DISTINCT field from table
    )
    ,
NextMax as (
    select field, ROW_NUMBER() over (order by field desc) as RN from Distincts 
    )
select * from NextMax where RN = 2

Upvotes: 0

Navneet
Navneet

Reputation: 447

SELECT TOP 1 field 
    FROM(
         SELECT DISTINCT TOP 2 field
           FROM table
             ORDER BY field DESC
    )tbl ORDER BY field;

Upvotes: 0

Christian Gollhardt
Christian Gollhardt

Reputation: 17014

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.

Upvotes: 7

Related Questions