SQL Order By and Update a Column – Mysql
I have the same issue listed in this SO question, but I have tried the solution and I am getting a SQL error 1288- I'm thinking I am poorly translating SQL server to SQL or this method has been deprecated.…
Calculating consecutive occurences in MySQL
I have a quick question in relation to windowing in MySQL SELECT Client, User, Date, Flag, lag(Date) over (partition by Client,User order by Date asc) as last_date, lag(Flag) over (partition by Client,User order by Date asc) as last_flag, case when…
Struggle with complex SQL query: are two nodes linked in a directed graph? – Postgresql
I've been stuck on this SQL query for a day now, so I'm throwing it up here and would appreciate any advice others can give. This is the problem: I want to generate a set of pairs of tags (named…
Postgresql – Simplifying WHERE (NOT) IN (…) AND WHERE (NOT) IN (…)
In this dbfiddle demo I have a DELETE FROM... WHERE at the end like so: ...... DELETE FROM data_table WHERE (location, param_id, ref_time, fcst_time) NOT IN (SELECT location, param_id, ref_time, fcst_time FROM sel1) AND (location, param_id, ref_time, fcst_time) NOT IN…
Mysql – how to rewrite a join query with CTE?
here I have tried to rewrite the query with cte cuz of good readability but when I try to rewrite the data is mismatched how to solve the problem for this? Query; select count(1) as rage_tap from ue_summary.summary_funnel_1066 s join…
cte table does not exist mysql
I am trying to calculate the number of users who have an active subscription on a particular day. I also want information related to the subscription plan they are on. I have a subscriptions table which includes the start date…
Mysql – Limit ordered SQL query by primary key
I have a query that orders the result by a specific none-unique column. Now I want to get the first N rows after a specific ID (the primary key). I have a table similar to this: ID Size 0 3…
Low performance of Common Table Expressions in PostgreSQL V14
I've recently switched from V11 to V14 of PostgreSQL, and some of my CTE queries, that used to take a few milliseconds, now takes a few seconds. The minimum query to reproduce this issue is the following : WITH MyTable…
How to divide annual amount to months in postgresql
I have an annual amount for each subscription with start_date structure; userid startDate(dd-mm-yyyy) amount 1 01-10-2020 120 1 01-10-2021 240 2 01-08-2020 60 I want to divide annual amount to months equally. Output table should be like; userid startDate(dd-mm-yyyy) amount…
Postgresql – Are joins in CTEs indexed or searchable in postgres
Does anyone know if Postgres is smart enough to search an in memory table in a binary fashion of some sort, or if not, is there a way to mark a column to tell postgres it is in order and…