I have this table column: I want the trailing numbers retrieved from the key names and stored in this manner: ID 1 2 3 4 I can retrieve it using: REGEXP_REPLACE((json_each_text(fixtures::json)).key, '.*?(d+)', '1')::int as ID But I want it retrieved…
I have the following query: SELECT DISTINCT FIRST_VALUE(business_id) OVER (PARTITION BY b.sub_category_id ORDER BY AVG(stars) desc, COUNT(*) DESC) business_id, sub_category_id FROM purchase_experience pe JOIN businesses b ON b.id = pe.business_id AND b.status = 'active' AND b.sub_category_id IN (1010 ,1007 ,1034…
There's a following table, called fields: And there's a dedicated table to store its values, called values I want to run a query to produce the following output: Finished | Faculity | Characteristic | Photo --------------------------------------------- 1 | Math |…
I am a beginner trying to build very simple inventory system using PHP + SQL I have 2 warehouse. I added 2 tables, 1 for each warehouse contains (item id)-(in)- (out) like shown below table1 item id in out item1…
I have two tables keywords and posts in my PieCloudDB Database. Each topic can be expressed by one or more keywords. If a keyword of a certain topic exists in the content of a post (case insensitive) then the post…
This is my first time make a post in here and appreciate any suggestion first! Basically, I got two tables that one is Incremental table and the other is Final table. In the First day loading, Incremental table is like…
I have a Regular Expression .*/(d+)(?![^(]*)) that it works in other program e.g., C#. And I want to use it in MYSQL, and it return error SELECT REGEXP_SUBSTR(myStr, '.*/(d+)(?![^(]*))'), c.* FROM mytable c; /* SQL Error (1139): Regex error 'unmatched…
I want to return records from a table in postgresql that when a distinct value in column 1 has more then one different values in column 2 spread over multiple records. As a simple example: table1: column1 - column2 1…
I am writing a frontend application that will provide a UI enabling users to make certain filter selections. We are creating a data visualization that will be populated by data objects fitting the user's filter criteria. For example, user might…
I have a main table, Table 1, that includes columns for student ID and some other info. There is also Table 2 which includes columns for student ID, survey question, and response. Essentially, I want to append Table 2 to…