I have the following query insert into T1 (code, code2, type, is_default, m_order, m_type, title, created_by) select t.* from (select a.code from (select code from T2 where type = 171 and value = 'ABC') a, select b.code from (select code…
Is there a way to make an entire row unique no matter what value the column is in? So, if 1 is in col a, and 2 is in col b, then if you were to insert 2 into col…
Table A: (columns: id (integer), name (varchar), ...) Table B: (columns: id (integer), a_id (integer), value (numeric), ...) Table C: (columns: id (integer), a_id (integer), b_id (integer), date (date), ...) SELECT A.name, SUM(B.value) AS total_value, COUNT(DISTINCT C.date) AS distinct_dates FROM…
We are trying to get filtered custom field data out of the Moodle database via SQL query. Unfortunately it's necessary to join multiple tables to get the data we need, because Moodle keeps its data this way. Also we can't…
I'm trying to find a way to calculate the average of the first commented day vs the first approved day of this data set. I've narrowed it down to one PR that I'd like to work on but I'd like…
I have a table containing areas whose size changes over time. The timeframe (in years) for which an area size is valid is stored in the columns "VALID_FROM" and "VALID_UNTIL". The timeframes for one area must not overlap. I want…
Let's say I have a database with columns name, last_name and type They're all strings, though type can only be TYPE_A, TYPE_B or TYPE_C I want a row in the database to be Unique when name and last_name already exist.…
In my hive table T the field A is a string in json format and A stores a value of {"c_e_i":"{"e_c_f":1}"}. I want to get c_e_i."e_c_f" So I used get_json_object(T.A, '$.c_e_i.\"e_c_f\"') but it doesn't work. what should I do? I…
There is a set of users. A person can have multiple users, but ref1 and ref2 might be alike and can therefore link users together. ref1 and ref2 does not overlap, one value in ref1 does not exist in ref2.…
I am using SQLAlchemy ORM and trying to figure out how to produce a PostgreSQL query like the following: SELECT resources.* FROM histories, jsonb_array_elements_text(histories.reported_resources) as report_resource_name JOIN resources ON resources.resource_name = report_resource_name WHERE histories.id = :id So far I got…