Categories
Database Development

Selecting only rows where one ID has the highest value of one field, grouped by two ID’s

It is given in the problem that occurrences has to happen more than once, but the Person(PID) should only be selected with the (Agent)AID having the highest BustCount. SELECT I.AgentID, I.PersonID, Count(*) AS BustCount FROM InvolvedIn I WHERE I.AgentID IS NOT NULL AND I.isCulprit = TRUE GROUP BY I.AgentID, I.PersonID HAVING COUNT(*) > 1; This […]

Categories
Database Development

#1170 – BLOB/TEXT column ‘variable’ used in key specification without a key length

I am trying to create a database table for a blog website, but I have an error from a text variable. the database structure looks like this: CREATE TABLE place( id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(30) NOT NULL UNIQUE, author VARCHAR(30) NOT NULL, city VARCHAR(30) NOT NULL, state VARCHAR(30) NOT NULL UNIQUE, latitude […]

Categories
Database Development

Generating a ‘history’ from daily dumps

I have a database with a geometry table, each row contains a geometry object, could be a line, a point or a polygon. it also contains the modifyDate of when it was last modified, userID of who last changed it and itemID, a unique id for the item. in the past, every time an item […]

Categories
Database Development

Creating map and use that within the mysql stored proc

I am working on a migration project and the data needs to be migrated from system A to system B. The id’s in system A and system B will be different. So maintaining a table with source and target column. As part of the migration, For each record from source A, I need to go […]

Categories
Database Development

What’s your experience to deal with the offboarding account?

I am thinking about doing the cleanup after the employee offboarding. Such as check across the multiple SQL instances and remove the offboarding account and the user on the database level. Since it’s across multiple SQL instances, I am considering using the dbatools PowerShell module. But not sure if it’s a good way. Anyone have […]

Categories
Database Development

Trying to join 2 tables to get a count of orders and grouped by email

I have two tables in DB Orders with columns Orders Id, Orders NO, Customer ID; Customer with columns Customer ID, Customer email. I need a count of the orders for each customer and group by email with output something like customeremail@email.com 23 SELECT Customer.Customer Email, Count Orders.Order ID, From Orders, Left Join Orders on Customer.Customer […]

Categories
Database Development

MariaDB: Insert records (list of tuples) into table if not exists

Currently I have several sql queries that look like this: INSERT INTO `test` (col1, col2) SELECT * FROM (SELECT ‘1’,’2′) AS tmp WHERE NOT EXISTS ( SELECT * FROM `test` WHERE col1=’1′ AND col2=’2′ ) LIMIT 1; INSERT INTO `test` (col1, col2) SELECT * FROM (SELECT ‘3’,’4′) AS tmp WHERE NOT EXISTS ( SELECT * […]

Categories
Database Development

Universal Table/Join Table A Good Idea?

Let’s say you have a list of entities: Post, Question, and Gallery and these entities can be liked and commented on by a user and also hold tags. Would would it be a good idea to compact things so there’s not a bunch of tables (Option 1) or is it better to create as many […]

Categories
Database Development

How to keep Sales records truthful in POS!

I’m developing a PHP/MySql retail shop management solution (for the first time!) that keeps records of sales and inventory. I have a problem, which will be better understood by the following chronology of events: 2021-01-19: Shopkeer_Joe purchases 2 chairs from supplier_Tom and enters the record into the software which creates produc_id ‘1’ in the database. […]

Categories
Database Development

How can I disable swappiness just for Mariadb and not for other applications on the same EC2 Instance?

I have an EC2 instance where DB(Mariadb) runs on the same EC2 instance as the other applications. MySQL documentation suggests to disable swappiness since DB doesn’t like swap. How can I disable swappiness just for Mariadb and not for all other users/apps ? Thanks Moe