I have two tables like: Desired output: I want to select all records in col1 of table_1 that aren’t in col1 of table_2. In actuality, table_1 has 65000+ rows and table_2 has around 2000 rows. My query: This returns no records. However, the reverse works as intended when I select all records in table_2 t…
Tag: sql
How can I remove characters in a string after a specific special character in snowflake sql?
I have a url column in my database. I want to remove all characters after ? . select regexp_replace(page_url,’?(.*)’, ”) as clean_url from demo But I’m getting the following error: Invalid regular expression: ‘?(.*)’, no argument for repetition operator: ? Answer Using LEFT…
SQL pivot and group by month
I’m trying to get the sum of production per month and pivot the month row to columns. Could you please tell me what is wrong with this code?’ Answer There were several items wrong in your query. Columns need aliases and your max() within the PIVOT was referencing an invalid column name
Do i need to cast integer param to string for varchar index to be used?
When i query a varchar column (indexed) against an integer number, it runs extremely slow. I thought mysql was able to infer this and cast the parameter to string, but when i use an integer for filtering it avoids the index. Is this ok? Is it a collation problem? Should i always cast manually integers to stri…
Simplifying SELECT statement
so I have a statement I believe should work… However it feels pretty suboptimal and I can’t for the life of me figure out how to optimise it. I have the following tables: Transactions [Id] is PRIMARY KEY IDENTITY [Hash] has a UNIQUE constraint [BlockNumber] has an Index Transfers [Id] is PRIMARY K…
SQL Table with two relations or table with one relation and repeated rows
This is an example, the names are fictitious. On the one hand, we have suppliers who provide products to shops: Suppliers On the other hand, we have shops that sell products to consumers: Shops There are shops that have their own prices like NiceShop or ShopShop, so they don’t have suppliers. But there …
Returning non-overlapping records within a date range
I have the following data. I have looked over a lot of threads about overlapping and non-overlapping dates but none seemed to help me. From this table with a SQL query I want to return the first record out of overlapping dates or basically I have been struggling a lot with this query and was wondering if this…
How to find difference between the integers ignoring null values
Data looks like this Expected Output I am not able to find a way to get the expected data. Answer Consider below approach if applied to sample data in y our question – output is
Is there some way to have an INSERT into one table always also INSERT into another table in SQL?
I have two tables that are related to one another. For simplicity let’s define them as products and productVariants: I would like the functionality that whenever a row is inserted into productVariants, a new row is inserted into products with id equal to the childProductId field in productVariants. For …
BigQuery – Solving a basic grouping/ aggregation error
I wish to output a sum of revenue per year per product, but also include a column with the year of introduction of each product (based on date first ordered). I can not figure out what I am doing wrong here as I can do each calculation separately but not it seems within the same query, I am sure this