I would like to know whether there is a way of importing JSON data from a MySQL DB to an R dataframe. I have a table like this: I would like to get the columns ‘id’ and ‘json’. I am using RMySQL package for getting the data from the db to an R dataframe but this gives me only the
How do I properly optimize this query?
I’m trying to optimize the following query. I’ve used EXPLAIN to troubleshoot but it keeps showing up in our logs as a non-indexed query. What am I missing? I’ve created indexes on all columns referenced, but it’s still showing up. Answer If you have a big table which contains millions…
Postgresql – create table with disjoint subclasses
I’m unsure how one can create a table on postgresql with disjoint subclasses. I have represented (a very simplified version of) my problem below in an ER diagram, showing the two subclasses and the attributes of each subclass. For the columns common to all rows (id, common1, common2), it’s clearly…
Sql Query to obtain unique values from a mixed data
I am new to MySql and have been trying to learn it for a project. I have a table as below (Table 1) and I need the output from it as Table 2. The output is ordered by CREATED_AT field and if there are multiple rows for the same FRUIT_TYPE, the field with the least CREATED_AT value is picked. For
MySQL to Microsoft SQL Server derived table syntax
I am trying to select data from a derived table created in the FROM statement. I have the following code in MySQL: I’m trying to do the same in Microsoft SQL Server, but this doesn’t work, with an error ‘incorrect syntax’. I’ve tried a few different combinations, and anything I c…
Trying to work out why my query is sluggish
So I have this query in C#, it takes about 3.6 seconds to complete. Here is both table designs Product review table: Review Table: I did have my where clause like so, but made no difference: I’m not sure why it’s slow, does anyone have advice to speed it up? Thanks More Info: Both tables have 6069…
PL/SQL procedure not compiling
I have a PL/SQL procedure that is not compiling. The errors are: Error(3,7): PLS-00103: Encountered the symbol “INTO” when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted deli…
How do I sort a table with non alphabetical values?
I’m a non-programmer and want to use the power of SQL to sort my inventory data. Here’s the sample table that I have. I want to turn this data to this. How can I query my table to have a result like this below Answer These two suggestions – if stick to the datasets given. If you’re on …
Logic to use in place of Aggregate function the WHERE clause
I am computing the frequency of values over a large table. since the result of the aggregate functions result in zero for some rows, I would like to simply skip such rows. I could have used NULLIF(..) but I do not want to return a zero value. And aggregate functions aren’t allowed in the WHERE clause. H…
SQL Filter unique results
I am trying to get an SQL statement that will output a unique part number eg no duplicates However I want the type as Purchased is the default and when there isnt a Purchased part it defults back to Manufactured. NOTE all parts can be purchased The result I require is to only show unique part numbers e.g. 1 t…