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 to say if it’s responsible for
Tag: sql
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 the example above the count should return User 1
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 i prevent it? Answer This happens
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 try to read out a
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, but I am having
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