Mysql – Which sql join should I use here to combine all the rows?
Table 1 : X , Y , Z 10, 12, 14 20, 17, 18 Table 2: X , Y, Z 100, 75, 80 375, 100, 122 I have 7 such tables. Expected outcome is combining only all rows. Which Join…
How can I DROP USER based on a regex in MySQL?
Is there a way to perform a DROP at specific prefix users (lets say tenant_* ) of MySQL? IE : DROP USER 'tenant_george'@'%'; DROP USER 'tenant_john'@'%'; etc
mysql 5.7, query to achieve this: Get least ID if repeating by message in sequence, order by ID
mysql 5.7, query to achieve this: Get least ID if repeating by message in sequence, order by ID. INPUT: id message 2485132 Process started 2485118 Process completed 2483660 Process started 2482165 Process started 2480689 Process started 2480686 Process completed 2479225…
JSON PATH – how to create multiple array elements on the fly
I am using SQL Server 'JSON PATH' functions to create nested JSON strings programmatically - without actually querying the database, since the strings placeholders (##..##) will undergo further processing elsewhere. This works fine so far with this type of query:…
PostgreSQL- Fill previous values with first non-null
I have 2 tables on my db: 1- scheduled services 2- services realized. On 2nd table, I have the value $ received for the service and the date the service was realized. On 1st table, I only have the scheduled…
Joining another table in MySQL inflates count number
I have three tables in a MySQL database, and after joining two of them together, the COUNT() function in my query no longer works properly, and returns inflated numbers instead. Please excuse my messy code as it is my first…
Mysql – LEFT JOIN only one match per ID
I'm trying to join two tables in a very peculiar way and I can't find the right query to achieve this. I want to keep ALL dt1 rows intact and join the first row for each FUND_ID with the respective…
Mysql – How to SQL select only most recent from related table
I have a table of requests from which I want to select everything, and I have a related table of positionReports where I want to select only the most recent report. The below SQL does what I want as long…
Postgresql – How to aggregate time series data on a postgres table for abitrary time range such that the returned result has no more than 100 rows
The table statuses has 3 columns id (uuid, PK) time (timestamp with time zone, NOT NULL) duration (integer, NOT NULL) The records it contains are not evenly distributed; there could be a single record in a day or thousands in…
PostgreSQL jsonb array of objects: how to filter by one of the fields and get the value of the other one
Suppose we have two columns: id, params. params is a jsonb column that contains arrays of objects like: [ { "prop": "a", "answer": "123" }, { "prop": "b", "answer": "456" } ] I need to create a query that returns…