CODEWITHSUNDEEP
sqlsqliteselect
Abc Xyz
Abc Xyz

Reputation: 1424

Sqlite, SELECT DISTINCT get multiple last rows

Just can't figure out what's wrong with this SQLite query.

I have ACCOUNTS table like this

subscr_id,txn_type,PAYMENT_START
A,        signup,  1
A,        pay,     2
A,        pay,     3
A,        cancel,  4
B,        signup,  1
B,        pay,     2
B,        pay,     3

and i want to output A,cancel,4 and B,pay,3, (only last value ordered by time (PAYMENT_START) for each unique subscr_id)

SELECT DISTINCT subscr_id,
   LAST_VALUE(txn_type) OVER (
   PARTITION BY subscr_id ORDER BY PAYMENT_START asc
   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last FROM ACCOUNTS;

When i want to execute this i get error:

Error: near "(": syntax error

Upvotes: 0

Views: 322

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Assuming that payment_start is not repeated for a given subscriber, you can do what you want using a correlated subquery:

select a.*
from accounta a
where a.payment_start = (select max(a2.payment_start)
                         from accounts a2
                         where a2.subscr_id = a.subscr_id
                        )

Upvotes: 1

Related Questions