I’m working on a report where I need data for current week, and the week before, and compare these two. I have a week column in my data, which are transactions, So my data looks something like: Amount …
SQL From the same table find records shared by common criteria
DB: postgres (PostgreSQL) 10.16 (Ubuntu 10.16-0ubuntu0.18.04.1) I have a table device_clients in which following data is present: id device_id client_id 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 6 6 1 7 7 …
PostgreSQL : Selecting all values of a key in jsonb
This is the current code I’m using. SELECT (agent->>0)::jsonb->>’key’, (agent->>1)::jsonb->>’key’ FROM table; But it’s possible that the jsonb column(agent) contains more than …
How to query for missing fields and associations and return what’s missing in PostgreSQL?
I am learning more raw SQL after coming from years of Rails and other ORMs, so have quite a ways to learn how to do complex queries efficiently. What I’m wondering here is _how to find all users which …
How to avoid that syntax : (Format(R.Date_VL,”) = Format(M.Date,”) => making macro very slow
I just realized that (Format(R.Date_VL,’yyyy/mm’) = Format(M.Date,’yyyy/mm’) is why my script is very slow. Do you know why and if there is a way to avoid that syntax ? its very very slow when I use …
How to group by two columns in different CASE clauses
I have a query that’s giving me the results I want, but for each item with a given ID_UNIDAD_EXPERIMENTAL there are two rows, one with column “Alt” with a value and column “Dap” with null, and the other one with “Alt” null and “Dap” with a value. My questi…
waitlist in postgres, deadlock
I’m trying to create a waiting list in Postgres. Minimal code: status is important, because I want to be able to notify users when they are applied after waitlisted. But don’t want to notify them based on that they are in the first n position if they wasn’t waitlisted at all. Services are li…
how to filter aggregate query ordered by date
I need to retrieve aggregate information (by means of SUM) on the basis of two separate dates – created_at and erased_at. The requirement is as follows: Column Description Month Month as YYYY-MM …
Not able to understand how to impose row_num() without using it through naive sql approach whereas rank and dense_rank works
I have below data set: mysql> select * from covid_test order by Country, Confirmed; +——+———+———–+ | SNO | Country | Confirmed | +——+———+———–+ | 19 | China | …
Why are my Hibernate `select` queries super slow?
I am using Hibernate in my Java based REST API. I am using HQL mainly because it is easy. Also for this project, I am loading data from the main table, its associated tables, their associated tables …