如何从仅含name字段的names表中获取倒数第二条记录？

Since SQL tables don’t have a default inherent order, we need to define an ordering to reliably retrieve the second last record. Below are several practical methods that work across different SQL databases, tailored to your specific dataset where the desired second last record is Zaryab.

Method 1: Using Window Functions (Modern SQL Databases) #

This approach uses ROW_NUMBER() to assign a sequential number to each record based on your desired order, then selects the row where the row number is one less than the total number of rows.

If you want to preserve insertion order (note: this depends on your database’s behavior—use an explicit ID/timestamp column for full reliability):

WITH numbered_names AS (
  SELECT 
    name,
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num
  FROM names
)
SELECT name
FROM numbered_names
WHERE row_num = (SELECT COUNT(*) FROM names) - 1;

If you prefer to order by the name column (adjust as needed for your use case):

WITH numbered_names AS (
  SELECT 
    name,
    ROW_NUMBER() OVER (ORDER BY name) AS row_num
  FROM names
)
SELECT name
FROM numbered_names
WHERE row_num = (SELECT COUNT(*) FROM names) - 1;

Method 2: Using LIMIT and OFFSET (MySQL, PostgreSQL, SQLite) #

For databases that support LIMIT and OFFSET, reverse the order of records, skip the first (last) record, and take the next one:

SELECT name
FROM names
ORDER BY (SELECT NULL) DESC -- Reverse insertion order
LIMIT 1 OFFSET 1;

Replace (SELECT NULL) with an explicit column like id DESC if you have an auto-increment ID for consistent results.

Method 3: Using Subqueries (Works in Most Databases) #

Exclude the last record first, then select the last remaining record:

SELECT name
FROM names
WHERE name NOT IN (
  SELECT name
  FROM names
  ORDER BY (SELECT NULL) DESC
  LIMIT 1
)
ORDER BY (SELECT NULL) DESC
LIMIT 1;

This first removes Hassan (the last record) from the set, then returns Zaryab as the new last record.

Key Note #

Always use an explicit ordering column (like an auto-increment id or created_at timestamp) instead of relying on insertion order. Without this, the database doesn’t guarantee record order, leading to inconsistent results.

内容的提问来源于stack exchange，提问作者Saani