I have a problem. I have a table with 6 million records in it. Every record has a column dateTime, and for my code I need the most recent 16 records in ascending order. This took too long to query directly out of the original table, so I created a view using the following query: This means that the view
Tag: mariadb
Replace “OR” on 2 indexes with a faster solution (UNION?)
I’m querying shopping-carts in a shop-system, like: I need to query records of c with a column which shows the number of carts which have the same user OR the same email. So I do: This works, but the problem is that the OR is very slow, because MySQL/MariaDB doesn’t use any key in the subquery: Ev…
SQL Query between a date range and a time range in MariaDB
I’m having trouble writing this query to give me any results. I’m using MariaDB as well. The DATE_ADD clause works just fine and gives results within that interval, but as soon as I add in the TIME function nothing is returned in the results. The CallDate format is 2021-09-21 HH:MM:SS I have tried…
TypeError: Argument 2 must be Tuple or List
I have built a GUI using TKinter. There I want to output the stored data and change them individually. Using the UPDATE command, I want to update the data in the table. I use MariaDB as database. I’m getting this error: Here is the complete function: Any suggestions where I’m doing wrong? Answer A…
sql prevent double booking with trigger
I am stumbled on constructing a DB table for hotel reservations. In my reservation db, there is a reservation start_datetime and end_datetime attribute to indicate booked period and I tried to prevent double booking with a trigger statemen, but I am certain that this method would be vulnerable to race conditi…
How to select other rows in GROUP BY
Sorry for the bad title. I don’t know how to summarize it better. I have a table with unix timestamps and other values, like the following. time value 1620916380 110 1620916440 100 1620916500 120 1620916560 120 1620916660 90 The entries are always one minute apart. I want to group entries into 5-minute …
SQL: How to select a single row based on a column matched against multiple possible values with preferred match order?
Using MariaDB. Say you have a table, one of the columns is color and is the primary key so it’s unique. I want a select statement to get one row based on color but i want to search for three possible color matches in a preferred order. A normal WHERE color=’red’ OR color=’blue’ O…
Trying to select data and then do a average on the selected data in mariadb
SELECT counts FROM (SELECT COUNT(ch_id) AS counts FROM tbl_warrants_checked WHERE status = “active” GROUP BY dateChecked); Answer This uses MariaDB 10.5, but should work with any version since ~10.2.2. If we assume every day has data, or we don’t care about the days which have no data, the f…
Find Latest Child Record (With Conditions) Per Parent Record
I have 2 tables with a parent/child hierarchy and am trying to pull the ID for the latest child record for each parent record, but also with conditions applied on the child record that is being pulled. Here’s what my tables look like: Now, let’s say I want to pull ID of latest QuizAttempt for each…
SQL DELETE FROM several tables
I have the following tables: dataset, links, files dataset has a field called tiComplete, if it is 0 then the record is incomplete, links and files both have a field “biDataset” that references the record in the dataset table. I’m trying to create a query that deletes all entries from datase…