在SQLite3中使用row_number()函数时遭遇语法错误求助

Hey there, let's break down why you're hitting that near "(": syntax error and fix it so you can deduplicate your demo table properly.

The Root Cause #

SQLite didn't support window functions like ROW_NUMBER() until version 3.25.0 (released in 2018). If your SQLite installation is older than that, the database engine won't recognize the ROW_NUMBER() OVER (...) syntax—which is exactly why you're seeing that error.

First Step: Check Your SQLite Version #

Run this simple query to confirm your version:

SELECT sqlite_version();

Solutions Based on Your Version #

1. If Your Version is 3.25.0 or Newer

Your original ROW_NUMBER() query is syntactically correct, but to actually get deduplicated records (keeping the latest creator entry per id), you'll need to wrap it in a subquery to filter for rn = 1:

SELECT *
FROM (
    SELECT demo.*, 
           row_number() over (partition by id order by creator desc) as rn 
    FROM demo
)
WHERE rn = 1;

This will return only the most recent (highest creator value) record for each unique id.

2. If Your Version is Older Than 3.25.0

You can achieve the same deduplication result using a correlated subquery instead of window functions:

SELECT d.*
FROM demo d
WHERE d.creator = (
    SELECT MAX(creator)
    FROM demo
    WHERE id = d.id
);

This query finds the maximum (latest) creator value for each id, then returns the full row that matches that id and creator pair.

Quick Note #

If creator isn't a value that can be ordered numerically/lexicographically to determine "latest" (e.g., it's a username instead of a timestamp), you might want to adjust the ordering logic—like using a created_at timestamp column if you have one instead of creator.

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