I have a column of type jSONB that have data like this: column name: used_filters row number 1 example: row number 2 example: I want to group the result to get the most frequent value for each one of the keys something like this: key most_freq category economic tags eco-friendly keyword 2050 the keys are not …
Tag: postgresql
When I perform an update – rows keep updating if I run the query again
I am updating a table as follows: Which gives the result ‘Query returned successfully: 6 rows affected, 26 msec execution time.’ If I run the same query again it still says gives the result ‘Query returned successfully: 6 rows affected, 26 msec execution time.’ – I would have tho…
Find minimum and show the area/values around it
I would like to find a minimum in a very long time series, but instead of show the whole series with 2000 points, or only 1 line, I want to show the 20 rows with the profit value before and after the minimum, result example : So, per company, find the minimum, then print the “area” around the mini…
Select second most recent date from inner join
I have this query : I get this result : How can I select only the second most recent created_at date from each unique display_name ? Answer You could use row_number to assign a sequence to your dates and apply this before joining, then include as part of your join criteria, such as: You could also apply this …
How to remove values from an array that exist in another array?
In PostgreSQL I have a table (Table) that contains an id column (ID) and another column (Values) that contains an array of strings. I have a select query (SelectQuery) that gets me an ID that matches Table.ID, as well as an array of values (RemoveValues). I would like to now remove from the Values array, any …
GROUP BY Subquery returns more than one row
I’m looking for a way to solve the following situation. I have a table that I need to return only one number for each “p.pays”, This query is supposed to list “nom from table Pays” where at least half of the “athlete” have are in the table “Resultat” but m…
How can I join two results
I have 3 tables purchase, air_transfers and sea_transfers. I have made the example code in db fiddle. My problem is on 6th paragraph where I don’t have any sea transfers, I get null as id and thats because I’m using id from sea transfers. What should I do to fix this? purchase table: air transfer …
SQL Query: finding cheapest car by company
Following YouTube tutorial “Learn PostgreSQL Tutorial – Full Course for Beginners”, I replicate teacher’s code but yields different result and cannot figure out why. Table is this simple: and so on, 1000 entries. Querying cheapest model from manufacturer, tutorial says: And it works pr…
Decluttering a SQL query
For a practice project i wrote the following query and i was wondering if there is way to make it more efficient than writing everything 12 times like a for loop for sql. EDIT I was given the 3 tables represented on the following database schema , and im trying to create a table with the total sales of dairy
How to convert integer[] to jsonb in a PL/pgSQL code block
How to convert integer[] to jsonb? Answer Use to_jsonb(): Or: array_to_json() is only still useful to get line feeds in json (not jsonb!). The manual: Converts an SQL array to a JSON array. The behavior is the same as to_json except that line feeds will be added between top-level array elements if the optiona…