How to delete some million rows in PostgreSQL?
In my system, a lot of data has accumulated in a table. Now I don't understand how to delete them. Then I realized that I need to use partitions, but I can't go back in time. Today i have 10…
Comma separated string similarity check in MySQL
I have a table with column that contain comma separated ids like : ids ---- 1,2,3,4 1,4,5 1,5 2 2,6,9 Now I need to sort these ids based on a given string similarity (common elements). For example if the string…
How to fill NULL values in a PostgreSQL column using the last non-NULL value
I have a PostgreSQL table with the following structure: CREATE TABLE cte1 ( entity_id INT, assignedtogroup INT, time BIGINT ); INSERT INTO cte1 (entity_id, assignedtogroup, time) VALUES (1, 435198, 1687863949740), (1, 435198, 1687863949741), (1, NULL, 1687863949742), (1, NULL, 1687863949743), (1,…
MySQL complex average
I am trying to come up with a MySQL script for averaging Price in the following data, by Price_Start_Date, but taking into consideration past prices that are still valid and taking the latest price for each Id. Better explained with…
In PostgreSQL how do you have multiple check constraints reference the same array of values?
Many of the columns in different tables in my database have the constraint between 1 and 3 (equivalent to any (array[1,2,3]) since they're all integers). This may need to be changed to allow 4s in the future, so I want…
Display a random flashcard out of a MYSQL database using python
I am making a web application using HTML, python and a MYSQL database. I currently have two tables: one that stores users and the other that stores flashcards. I want to add the feature of 'practising' flashcards by displaying a…
Phpmyadmin – SQL Search – Strange Behaviour getting date of max value
So I'm running a query against a MariaDB in PHPMyAdmin, its been working fine until we have got to a new month and for the life of me I can't see the issue. The table is in the following format…
Postgresql – Calculate delta between two tables
I need to calculate delta between two tables. See the example: First, I have tables A and B table A ID Value Id 1 10 Id 2 20 Id 3 30 Id 4 40 table B ID Value Id 1…
Php – how to write a line breaker for sql data in html
I want to show the data which is stored in sql column like a package details of a tour. when i am calling that column it's displaying details but it's not showing in clean and neat way. It's displaying like…
Joining multiple junction tables and grouping results in a JSON-formatted array
Say I have a table of people: +----+------+ | id | name | +----+------+ | 1 | John | | 2 | Mary | | 3 | Jane | +----+------+ And various tables for clothing of various types, e.g. a…