I have a sqlite3 DB with a few rows in it. When I try to fetch data from it in Python, fetchall returns an empty list. The data is being inserted in a different part of the project fine. I verified this by opening the DB in “DB Browser for SQLite” and running the following command. This returned t…
Tag: sql
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…
SQL combining tables to single rows
A problem with combining tables into single rows. TABLE GAMES: ID player1_ID player2_ID des 11 23 68 something 82 34 23 whatever 302 13 34 mmmmmmmm TABLE PLAYERS: ID alias 23 Jeex 34 Jack 68 Jill 13 John The key I start my query with is one of the Player ID’s. I want to show all the games that Player
Split text and digits by delimiter using regexp splitpart
From the table below I would like to split the config_json column into 2 columns as below The regex part of the code currently looks like this: However, there are some remains such as below: Thus, the formula above still needs to split by not just true,false or null, but also by digits or string. How can I fi…
Filtering null values when casting – using CASE statements
I’ve been trying to query rows with a date field up to a threshold. For reasons beyond my control, all fields are varchar. I’m trying to filter out null values which case an error when cast. My latest attempt: The above code still hits an error trying to cast “” to a date. I thought th…
Count distinct id between months previous year and same months current year Bigquery
I have a dataset in bigquery which contains order_date: DATE and customer_id. How can I count distinct customer_id between the months of the previous year and the same months of the current year? For example, from 2020-01-01 to 2021-01-01, then from 2020-02-01 to 2021-01-01, and so on until the current date a…
Compare COUNT results before and after operation/load in SQL
I need help for one of my cases. Lets say that I have one table with only one column named CustomerID with 1500 records. The CustomerID table is loaded in DB 2 times per day – 10am and 10pm I want to compare the CustomerID table in the morning (10am) with the one in (10pm) SELECT COUNT(*) from CustomerI…
Exclude from resultset based on condition
Given the following table FOO; How can I select all the records except where org = Y and status = CLOSED So the resultset looks like: Answer You can do this either of these ways:
Aggregated row count differences between tables
I have two MySQL tables A and B both with this schema ID entity_id asset asset_type 0 12345 x 1 .. ……… ….. ………. I would like to get an aggregated top 10/50/whatever entity_ids with the largest row count difference between the two tables. I think I could do this manual…
Assign incremental id based on number series in ordered sql table
My table of interview candidates has three columns and looks like this (attempt is what I want to calculate): candidate_id interview_stage stage_reached_at attempt <- want to calculate 1 1 2019-01-01 1 1 2 2019-01-02 1 1 3 2019-01-03 1 1 1 2019-11-01 2 1 2 2019-11-02 2 1 1 2021-01-01 3 1 2 2021-01-02 3 1 3…