SQL代码报错排查：使用ROW_NUMBER() OVER时出现语法错误

Hey, let's break down why your SQL is throwing that syntax error and fix it step by step.

1. Fix the column alias syntax first #

SQLite doesn't support the rn = ROW_NUMBER() syntax for assigning column aliases—you need to use the standard AS keyword instead. Your corrected base query should look like this:

SELECT ROW_NUMBER() OVER (PARTITION BY s.stop_id, s.stop_name ORDER BY s.stop_id, s.stop_name) AS rn
FROM stops s;

This is the proper way to name the window function result column in SQLite (and most SQL databases, for that matter).

2. Verify your SQLite version supports window functions #

The ROW_NUMBER() window function was added in SQLite 3.25.0. If you're running an older version, this will throw an error even with the corrected syntax. Check your version with this query:

SELECT sqlite_version();

If your version is below 3.25.0, you'll need to update your SQLite library or the tool you're using to access the database.

3. Bonus: Actually remove duplicate rows #

Since your end goal is to eliminate duplicates, generating the row number is just the first step. Here's how to filter out duplicates using a CTE (Common Table Expression):

WITH ranked_stops AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY stop_id, stop_name ORDER BY stop_id) AS rn
  FROM stops
)
SELECT stop_id, stop_name -- List all the columns you need except rn
FROM ranked_stops
WHERE rn = 1;

The PARTITION BY clause groups rows by your duplicate criteria (stop_id + stop_name), and ROW_NUMBER() assigns a unique number to each row in the group. Filtering for rn = 1 keeps only one row per unique group.

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