I have the following tables: COURSE_PREREQUISITES My question is: How can I obtain all the course ids a student needs to take before he can take course id 6? Intended Answer: I should obtain the course id of 1, 2, 3, 4, 5. Attempts Made: I have tried selecting the prerequisite_course_id of the intended final …
Tag: postgresql
Select row in group with largest value in particular column postgres
I have a database table which looks like this. I need to group this table by id and select particular row where time_point has a largest value. Result table should look like this: Thanks for help, qwew Answer In Postgres, I would recommend distinct on to solve this top 1 per group problem: However, this does …
Aggregate rows according to JSON array content
I have a PSQL table with json tags, that are always strings stored in a json array : I would like to query, for instance, the count of entries in the table containing each tag. For instance, I’d like to get : I tried but if does not work, since it gives I guess I need to get the list
Find entity with the biggest difference in a given column
I hava table profiles and I want to get the account with the most gained followers. I am using Postgresql. Note current record and previous record are all on the same table but with different timestamps. I have the logic in my mind but honestly I don’t know how to express/think of it in terms of a sql q…
Why SUM values are wrong in postgresql query?
I use postgresql and I have trouble in forming the correct query to get the desired result. I have a query And it gives me result as below. I wish to get the result like So I tried to SUM the third column and so changed the query a bit like this, But the result is like below, Fist this
PostgreSQL SQL query to find number of occurrences of substring in string
I’m trying to wrap my head around a problem but I’m hitting a blank. I know SQL quite well, but I’m not sure how to approach this. My problem: Given a string and a table of possible substrings, I need to find the number of occurrences. The search table consists of a single colum: searchtable | pattern TEXT PR…
SQL – sum a column with common id, then coalesce total with another column
I know somebody can tell me what I’m doing incorrectly here. This is a postgresql db. I’m trying to first sum the values in a column that share the same ID, and then add that total (coalesce) with …
Postgresql Convert SQL XML Coding to SQL JSON Coding
How to convert XML SQL Coding to JSON SQL Coding. Example: SELECT XMLELEMENT(NAME “ORDER”, XMLFOREST(PURCHASE_ORDER AS OD_NO)) AS “XMLELEMENT” FROM TBL_SALES Now how to convert …
SQL – Postgres string agg is giving duplicates
Im trying to collect the foreign key mapping from system tables. And I used this below query. query 1: But this won’t provide the Primary tables columns that are pointing as Fk. So I found this query on Stackoverflow. query 2 Now this query gives individual columns. Lets say there is a FK like (logid, i…
Postgres how to return values in between Min column and Max column
totally new in learning postgres/SQL I have a table called error_table that looks like this: but I am having a hard time generating a query on how to do this dynamically when my input number is 160, I would like to return the error_percent value 4.6 because 160 sits between the min column 100 and max column 2…