Sqlite Equivalent of Row_Number() Over (Partition by ...)

SQLITE equivalent of ROW_NUMBER

In SQLite 3.7.11 or later, you can use GROUP BY with MAX() to select which row in a group to return:

SELECT *, MAX(timestamp)
FROM events
GROUP BY id, eventtype

In earlier versions, you have to look up some unique ID of the largest row in a group with a subquery (as in you answer).

sqlite equivalent of row_number() over ( partition by ...?

I know this question is old, but the following SQLite statement will do what Rory was originally asking for in one statement - Delete all records for a given UserId that are not the 10 most recent records for that UserId (based on SomeDate).

DELETE FROM data
WHERE AnId IN (SELECT AnId
               FROM data AS d
               WHERE d.UserId = data.UserId
               ORDER BY SomeDate DESC
               LIMIT -1 OFFSET 10)

How to use ROW_NUMBER in sqlite

Try this query

select id, value, (select count(*) from tbl b  where a.id >= b.id) as cnt
from tbl a

FIDDLE

| id | value | cnt |
--------------------
|  1 |   yes |   1 |
|  3 |   yes |   2 |
|  4 |   yes |   3 |
|  6 |   yes |   4 |
|  9 |   yes |   5 |

SQL error: ROW_NUMBER() OVER (PARTITION

In SQLite, you can typically use rowid:

select s.*
from stops s
where s.rowid = (select min(s2.rowid)
                 from stops s2
                 where s2.stop_id = s.stop_id and s2.stop_name = s.stop_name
                );

I'm not sure if this is what you really need. But this seems to be what you want to do with row_number(). If this doesn't hit the spot, then ask another question with sample data and desired results.

How can the same function be provided without using the row_number() function in sqlite?

I would expect recipes to have unique names. If so, then the outer quer is simply not needed:

SELECT rt.*
FROM SyncRecipeIngredientTable sr JOIN
     RecipeIngredientTable ri 
     ON ri.recipe_ingredient_id = sr.recipe_ingredient_id JOIN
     RecipeTable rt
     ON rt.recipe_id = sr.recipe_id
WHERE ri.recipe_item_name in ('patates', 'soğan', 'su')
GROUP by rt.recipe_id
HAVING COUNT(*) >= 3;

If recipes can have duplicated names, then you can use a correlated subquery:

WITH rt AS (
      SELECT rt.*
      FROM SyncRecipeIngredientTable sr JOIN
           RecipeIngredientTable ri 
           ON ri.recipe_ingredient_id = sr.recipe_ingredient_id JOIN
           RecipeTable rt
           ON rt.recipe_id = sr.recipe_id
      WHERE ri.recipe_item_name in ('patates', 'soğan', 'su')
      GROUP by rt.recipe_id
      HAVING COUNT(*) >= 3
     )
SELECT rt.*
FROM rt
WHERE rt.recipe_id = (SELECT MAX(rt2.recipe_id)
                      FROM rt rt2
                      WHERE rt2.recipe_name = rt.recipe_name
                     );

Looking for an equivalent to INTERSECT ALL in sqlite

You can simulate INTERSECT ALL by adding another column to your resultset with ROW_NUMBER() window function:

WITH cte AS (
  SELECT p_urls, ROW_NUMBER() OVER (PARTITION BY p_urls) rn 
  FROM Tags WHERE p_tags = 'foo' 
  INTERSECT
  SELECT p_urls, ROW_NUMBER() OVER (PARTITION BY p_urls) rn 
  FROM Tags WHERE p_tags = 'bar'
)
SELECT p_urls FROM cte;

Or:

WITH cte AS (
  SELECT p_urls, 
         ROW_NUMBER() OVER (PARTITION BY p_tags, p_urls) rn 
  FROM Tags 
  WHERE p_tags IN ('foo', 'bar')
)
SELECT p_urls 
FROM cte
GROUP BY p_urls, rn
HAVING COUNT(*) > 1;

See a simplified demo.

How to mimic a ROW_NUMBER() functionality without using ROW_NUMBER()?

This is all kinds of ugly, but with no windowing functions this is the only way I know of to mimic the row_number function:

select
  t.Name, t.DateAdded, t.Amount,
  ( select count (*)
     from MyTable u
     where
       t.Name = u.Name and
       t.DateAdded >= u.DateAdded
  ) as row_number
from MyTable t

Results:

John    2015-10-12  100 1
John    2015-10-15  50  2
Aaron   2015-09-20  10  1
Paul    2014-12-20  24  1
Paul    2014-12-23  32  2

---

Related Topics