I am running a Postgres query with a CASE expression in a join condition. The query takes a long time to run. Is there a better way to optimize this query? Code snippet: Answer For proper answer attach full query, table structure (with indexes) and execution plan. Original CASE is quite complicated, but hard …
Matching multiple columns based on groupID from another table’s data
I have 2 tables, one where I have sets of information that is organized by group ID’s. This group ID means all parts must be in that group for that machine to be an option: Machine Part Group 1 Part Group 2 GroupID Mach A Part M Part A 1 Mach A Part M Part B 1 Mach A Part
Count users with a condition in MYSQL
I have a table ‘helpdesk’ (detail below) that lists tickets. I’m trying to get the top 10 users who report issues, however there is a complication in the fact that users can report an issue for another user (think secretary for their boss as an example). The table structure is… Using t…
How can I return the latest child of a parent table in SQL
I have a parent table “A” looking like Main Status Reference 1 0 AA 2 1 AB 3 0 AC 4 0 CA and a child table “B” (related by ‘Main’) looking like ID Main C-Status Timestamp 1 1 c 3 2 1 b 4 3 2 a 4 4 2 b 5 5 2 c 6 6 3 c
Node.js rounds my sql result. how do i prevent this?
I am requesting a bigint from my database. However, Node.js alters the value of my request, so that my program doesn’t work. Here’s my code: The console logs: However, if i run the same statement in PHPmyAdmin, it looks like the following: it returns: Why does Node.JS round the value up and how do…
SQL: finding (multiple) best students of a professor by grade
I have to find the students with the best grade of each professor with this given table “x”: Prof Student Grade A 1 1.0 A 2 1.0 A 5 5.0 A 6 1.3 B 3 1.2 B 4 2.0 … … … The result should look like this: Prof Student Grade A 1 1.0 A 2 1.0 B 3 1.2
ValueError when reading dates from hfsql database using ODBC
With some struggles I connected to a hfsql server using ODBC. I’ve tried both pypyodbc and pyodbc. My goal is to get some insights in the data (and visualize some aspects). For some planning visualization I need to read out some of the data, which works fine for most tables and columns. However, when I …
Pivot by Country Name and Flag in Postgresql
and I am trying to cross tab it like this: Here is my SQL query, I am not able to do it since I do not have permissions to create extensions (tablefunc) Is there an alternate way? Can you please help? Thank you. Answer As you ave only 2 values, you can make the classical approach to pivot country |
How to Count Entries on Same Day and Sum Amount based on the Count?
I am attempting to produce Table2 below – which essentially counts the rows that have the same day and adds up the “amount” column for the rows that are on the same day. I found a solution online that can count entries from the same day, which works: It is partially what I am looking for, bu…
How to split an array in Clickhouse based on custom condition
I am trying to find a way to split my array when the next element is smaller than the previous element. Here is some sample data [100, 200, 500, 100, 150, 200] I need to convert this to [[100, 200, 500],[100, 150, 200]] I am trying to calculate the running difference of the array and I need to handle the