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 eventsGROUP 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).

I'm a bit late to this question, but I wasn't satisfied with the current answers as they mostly use correlated subqueries which can seriously ruin performance.

In many situations, single-column analytical functions can simulated using a standard join:

SELECT e.*FROM events eJOIN(    -- Our simulated window with analytical result    SELECT id, eventtype, MAX(timestamp) AS timestamp    FROM events    GROUP BY id, eventtype) winUSING (id, eventtype, timestamp)

In general, the pattern is:

SELECT main.*FROM mainJOIN(    SELECT        partition_columns,        FUNCTION(analyzed_column) AS analyzed_column    FROM main    GROUP BY partition_columns) winUSING (partition_columns, analyzed_column)

These simulated windows aren't perfect:

1. If your data has ties for your analyzed column result then you may need to remove duplicates from your result set. Otherwise you'll select every row from your partition that matches your analyzed column result.
2. If you analytical function requires ordering by more than one column, you will need to use correlated subqueries instead. The other answers can be modified to achieve the desired result.

I got help from the following link:sqlite equivalent of row_number() over ( partition by ...?

Here is what i came up with:

select * from events E1 where timestamp in(select timestamp from events E2 where E2.id = E1.id and E2.eventtype=E1.eventtype                         order by E2.timestamp desc                         LIMIT 1  );

Also with SQL SERVER, I am thinking of this solution (as I have no way to test)

select id,eventtype,value,ROW_NUMBER() over (PARTITION BY id,eventtype,order by timestamp desc) AS RN  from events where RN<=1 ;