SQL选取第N条记录返回错误结果，关联Product与SalesProduct表求助

Hey there! Let's work through how to correctly fetch the Nth record in SQL using your Product and SalesProduct tables as examples. The most common reason you're getting wrong results is usually either not specifying an explicit sort order (SQL tables don't have a built-in, guaranteed order) or using database-specific syntax incorrectly.

First, the Critical Rule: Always Use ORDER BY #

Before trying to grab the Nth record, you must define an ORDER BY clause. Without it, the database returns rows in an arbitrary order—so your "Nth record" could change every time you run the query.

Solutions by Database Type #

Below are working examples tailored to popular databases, using your tables:

1. MySQL/MariaDB

Use LIMIT + OFFSET (note: OFFSET starts at 0, so to get the Nth record, use OFFSET N-1):

-- Get the 3rd record from SalesProduct, sorted by ID
SELECT * FROM SalesProduct
ORDER BY ID ASC
LIMIT 1 OFFSET 2;

-- Get the 2nd record from Product, sorted by ProductCode (case-insensitive)
SELECT * FROM Product
ORDER BY LOWER(ProductCode) ASC
LIMIT 1 OFFSET 1;

2. PostgreSQL

You can use the same LIMIT/OFFSET syntax as MySQL, or use a window function for more complex scenarios:

-- Simple LIMIT/OFFSET approach for 3rd SalesProduct record
SELECT * FROM SalesProduct
ORDER BY ID ASC
LIMIT 1 OFFSET 2;

-- Window function approach (great for filtering multiple rows later)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS row_num
    FROM SalesProduct
) AS sorted_sales
WHERE row_num = 3;

3. SQL Server (2012+)

Use OFFSET ... FETCH NEXT or window functions:

-- Get 3rd SalesProduct record sorted by SalesNo
SELECT * FROM SalesProduct
ORDER BY SalesNo ASC
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

-- Window function alternative
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY SalesNo ASC) AS row_num
    FROM SalesProduct
) AS sorted_sales
WHERE row_num = 3;

4. Oracle

Oracle doesn't support LIMIT, so use nested queries with ROWNUM (or window functions for 12c+):

-- Pre-12c: Nested query to sort first, then filter ROWNUM
SELECT * FROM (
    SELECT *, ROWNUM AS row_num
    FROM (
        SELECT * FROM SalesProduct
        ORDER BY ID ASC
    ) AS sorted_data
) AS final
WHERE row_num = 3;

-- Oracle 12c+: Window function approach
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS row_num
    FROM SalesProduct
) AS final
WHERE row_num = 3;

Common Mistakes to Avoid #

• Skipping ORDER BY: Without sorting, your "Nth record" is meaningless—results will be unpredictable.
• Incorrect OFFSET value: Remember OFFSET starts at 0. If you want the 5th record, use OFFSET 4, not OFFSET 5.
• Oracle ROWNUM misusage: Never use WHERE ROWNUM = N directly—ROWNUM is assigned before sorting, so it won't return the correct row. Always nest the sorted query first.

Example with Joined Tables #

If you want to fetch the Nth sales record along with product details, here's a MySQL example for the 4th sales record:

SELECT sp.ID, sp.SalesNo, p.ProductCode, p.Name
FROM SalesProduct sp
JOIN Product p ON sp.ProductID = p.ProductID
ORDER BY sp.SalesNo ASC
LIMIT 1 OFFSET 3;

This will return the record with SalesNo = S0004 and the associated product name "Computer".

内容的提问来源于stack exchange，提问作者Nasir Islam Sujan