SQL Server JSON Queries
SQL query to check if a key exists and its value is null in JSON column Extending this question, if I want to query for multiple keys, it works fine for me with both AND and OR in a SQL…
Postgresql – Combine two tables doing row count based on hour value in timestamp
I've got two tables: utilities: id timestamp action 901 2024-08-11 09:59:25.000 on power 902 2024-08-11 09:59:35.000 on water 903 2024-08-11 09:59:55.000 off power 904 2024-08-11 10:01:25.000 on gas 905 2024-08-11 10:02:35.000 off water 906 2024-08-11 10:11:18.000 off power 907 2024-08-11…
Postgresql – How does a Recursive CTE know to only process records added during the last iteration (and not looking at all of them)?
Consider this [v good] example from Using recursive CTE to generate a range of data WITH RECURSIVE date_range AS ( SELECT '2023-01-01'::timestamp AS date UNION ALL SELECT date + interval '1 day' FROM date_range WHERE date < '2023-12-31' ) SELECT…
Mysql – Get Records between dates and exist at least 3 times
I have table with over 35 million email records. I need to get all of the records between a date range and also exist at least 3 times in the table. 1 email may have more than one property but…
Group multiple events for ID in postgreSQL data
I'm trying to select pairs of events from my data. I have a data that looks like this: create table test(id,status,created)as values (1, 'on','2024-09-01'::date) ,(2, 'on','2024-09-02'::date) ,(1,'off','2024-09-03'::date) ,(1, 'on','2024-09-04'::date) ,(2,'off','2024-09-05'::date) ,(3, 'on','2024-09-06'::date) ,(1,'off','2024-09-07'::date) ,(4,'off','2024-09-08'::date); I would like to have data…
Postgresql – How to get list of customized options in postgres
In postgres I can define custom option using set x.x = 12; I can also read it using following query SHOW x.x; But I couldn't find any command to list all such defined options. Is there anything I can use…
MYSQL GROUP BY most frequent value for each ID
I have following 2 tables: Sales and Menu. SALES TABLE customer_id product_id A 1 A 2 A 2 A 3 A 3 A 3 B 1 B 1 B 3 B 3 B 2 B 2 C 3 C 3…
Postgresql – Does serial data type assign unique values to each thread trying to insert?
I am getting following error when inserting data in Postgres in a multi-threaded env, I am relying of serial data type to assign PK assuming it is thread-safe, I lose data when I get following error, how can ensure that…
Postgresql – How can I request unique records (DISTINCT) together with a mechanism for selecting the desired value among the same ones?
I need to select rows from the database that are unique by date. Ideally, there will be very few such strings, but if they come across, then you need to select only one value among them. DISTINCT assumes just a…
Postgresql – Need to create group based on actions
I have a table and I am only interested in two columns to perform two actions (Ignore or Good), and based on actions I need to keep only "Good" rows. I have tried several SQL windows functions but not able…