Oracle查询报错ORA-00923：获取最小Opened值对应全列数据失败

Hey there! I get it—switching from SQLite to Oracle can throw you off since they handle some SQL syntax differently. Let's figure out why that error pops up and get you the data you need.

Why You're Seeing ORA-00923 #

SQLite has some relaxed, non-standard behavior that lets you mix regular columns (like act_id, balance) with aggregate functions (like MIN()) in a SELECT without grouping. But Oracle strictly follows ANSI SQL rules: if you include an aggregate function in your SELECT, all non-aggregate columns must be in a GROUP BY clause.

In your case, grouping by all columns would just return every row (since each row is unique via the primary key act_id), making the MIN(Opened) useless. That's why Oracle throws the "FROM keyword not found where expected" error—it can't parse your invalid mixed syntax.

Solutions to Get the Row with Minimum Opened Date #

Here are a few clean, Oracle-friendly ways to get what you want:

1. Subquery to Find Minimum Date, Then Match Rows

This is the most straightforward approach, and it works in all Oracle versions. It first gets the smallest Opened value, then grabs all rows that match that date:

SELECT a.*
FROM Account a
WHERE a.Opened = (SELECT MIN(Opened) FROM Account);

If multiple rows have the same earliest Opened date, this will return all of them—perfect if you need every matching record.

2. Window Functions (RANK/ROW_NUMBER)

For more control (like handling ties explicitly), use window functions. RANK() will assign the same rank to rows with the same Opened date, while ROW_NUMBER() will give each a unique number (great if you only want one row even if there are ties):

-- Return all rows with the minimum Opened date
SELECT act_id, Opened, Balance
FROM (
    SELECT 
        *,
        RANK() OVER (ORDER BY Opened ASC) AS date_rank
    FROM Account
) ranked_accounts
WHERE date_rank = 1;

-- Return just one row (random if ties exist)
SELECT act_id, Opened, Balance
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY Opened ASC) AS date_row
    FROM Account
) ranked_accounts
WHERE date_row = 1;

3. Oracle 12c+ Shortcut: FETCH FIRST

If you're using Oracle 12c or later, you can use this concise syntax to get the top rows ordered by Opened:

-- Get one row with the earliest Opened date (random if ties)
SELECT *
FROM Account
ORDER BY Opened ASC
FETCH FIRST 1 ROW ONLY;

-- Get all rows with the earliest Opened date
SELECT *
FROM Account
ORDER BY Opened ASC
FETCH FIRST 1 ROW WITH TIES;

Quick Note on SQLite vs Oracle #

SQLite lets you take shortcuts with aggregate functions, but Oracle enforces stricter SQL standards to avoid ambiguous results. Once you get used to these rules, you'll find Oracle's consistency helps avoid unexpected data down the line!

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