SELECT queries in a loop

user_11932012 0
Viewed 29

I am working on an sqlite3 shell.

SELECT * FROM tasks WHERE name in ("TaskA", "TaskD") LIMIT 5;

The above statement will print the first 5 rows only. Is there a way to write this statement so the first 5 rows from each of "TaskA" and "TaskD" results are printed?

In essence, I am trying to write a loop akin to:

FOR task in ("TaskA", "TaskD") SELECT * FROM tasks WHERE name = task LIMIT 5;

But I do not know the proper syntax.

sqlite
1 Answers

Use ROW_NUMBER() window function:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY rowid) rn 
  FROM tasks 
  WHERE name IN ("TaskA", "TaskD")
)
WHERE rn <= 5

I used the column rowid to define the order of the rows.
If there is another column in your table, like a date column, that can be used to define the order, you can replace rowid with that column.

user_104988280
Related