Here is the code I have which checks if there is more than 5000 entries after an insert, then deletes all entries which are greater than 2 hours old I am getting you have an error in your mysql syntax near IF (SELECT … DELETE FROM … I am using MariaDB, can someone help me understand where is the e…
Tag: sql
Remove subset rows of other rows in SQL
I’ve this table below that includes ID, and five indicator columns: x1, …, x5: I need to remove duplicates based on this logic: For each ID, we look at the values of x1, …, x5, and we remove the ones that are subset of other row. For example, for ID=1, row #3 is a subset of row #2, so we
SQL query optimisation by replacing subquery
The following query works and gives me the expected result but i’d like to optimise it. Is there a way to avoid the subquery, using only the conditions in the main query to arrive at the same result: PS: In PLSQL, NVL function lets us substitute a value when a null value is encountered Thanks for any in…
How to JOIN two table in BigQuery, but the join parameter are nested
sorry for the newbie questions, just started learning SQL. I have two tables: sessions items sessions table has questions (RECORD, Repeated), and inside questions there’s item_id (String) items table has topics (RECORD, Repeated), and inside topics there’s prior_difficulty (String). items table al…
HIVE CBO. Wrong results with Hive SQL query with MULTIPLE IN conditions in where clause
I am running one SQL query in Hive and it gives different results with CBO enabled and disabled. The results are wrong when CBO is enabled (set hive.cbo.enable=true;). Prerequisites: Apache Hadoop 2.10.1 + Apache Hive 2.3.6 installed. (I tried to reproduce the issue with Apache Hive 3+ version and Hadoop 3+ v…
Splitting a nested dict-like varchar column into multiple columns using SQL presto
In my table I’ve a column, which is a varchar but has a nested dictionary-like format (three nested levels). Some entries have multiple key-value pairs (customer ID & name), while some just have a single entry (customer ID). For example: I need a query that will break out the the column into a table…
Got invalid expression error , while inserting JSON data into VARIANT column
Trying to insert multiple rows into a snowflake table Have tried replacing ‘$$’ with single quotes Getting error: SQL compilation error: Invalid expression [PARSE_JSON(‘{“def”:1}’)] in VALUES clause What am I doing wrong? Answer You cannot use PARSE_JSON in the values secti…
how to get json result to fixed number by grouping in postgresql?
I want to get expected results to one query execution. the “where” clause should one value. so I can’t use this query as sub-query of main-query. could you tell me how to do it? Sample sql query: Expected results: results [{“id”:1,”name”:”hello”},{“i…
Loop custom type object ion procedure
I created the following type: Inside of a procedure then I accept this parameter and want to loop through it: I keep getting this error though: Answer It is possible that you want to create a collection type and then pass that collection to your procedure If that is not what you are looking for, please edit y…
Select where all columns in list item
How do I do the equivalent of the following in SQL Server? I only want to match rows where There can be an arbitrary number of match conditions. The only solution I have been able to find is the above, but doubt it would scale to 1000s of dynamic values. Answer Put those value in temp table or use Table