Clever Geek Handbook

Equivalent to SQLITE ROW_NUMBER

I have a table in SQLite

/* Create a table called NAMES */
CREATE TABLE EVENTS(Id integer , Eventtype integer,value integer,Timestamp DATETIME);

/* Create few records in this table */
INSERT INTO EVENTS VALUES(1,2,1,'2009-01-01 10:00:00');  --ROW1
INSERT INTO EVENTS VALUES(1,2,2,'2007-01-01 10:00:00');  --ROW2
INSERT INTO EVENTS VALUES(2,2,3,'2008-01-01 10:00:00’);  --ROW3

The request must include ROW1 and ROW3. The request must use the most recent string-based timestamp for duplicate ID and Eventtype combination. ROW1 and ROW2 have the same event type and ID, but ROW1 is the last one, so it must be selected.

+3


source to share


3 answers


In SQLite 3.7.11 or newer, you can use GROUP BY with MAX () to select which row in the returned group:

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


In earlier versions, you need to find the unique id of the largest row in the subquery group (as in the answer).

+8


source


I need help with the following link: sqlite equivalent to row_number () over (partition by ...?

This 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 about this solution (since I have no way to check)

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


source


I am a little late for this question, but I am not satisfied with the current answers as they mostly use correlated subqueries which can seriously hurt performance.

In many situations, single column analytic functions can be modeled using a standard join:

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

In general, the template looks like this:

SELECT main.*
FROM main
JOIN
(
    SELECT
        partition_columns,
        FUNCTION(analyzed_column) AS analyzed_column
    FROM main
    GROUP BY partition_columns
) win
USING (partition_columns, analyzed_column)

These simulated windows are not perfect:

  • If your data has bindings for your parsed column result, you may need to remove duplicates from your result set. Otherwise, you will select each row from your section that matches your parsed column.
  • If your analytic function requires ordering in more than one column, you will need to use correlated subqueries. Other answers can be modified to achieve the desired result.
0


source






More articles:


All Articles