My database has a column that contains a JSON array like this: WITH dataset AS ( SELECT * FROM (VALUES ('1', JSON'[{ "name" : "foo" }, { "name" : "bar" }]'), ('2', JSON'[{ "name" : "fizz" }, { "name" :…
I would like to have a query with a result like below: 1000 2000 3000 or 1 3 5 so on..I want to control results from lists for instance. SELECT 1 — I know that I can use SELECT like…
Given the table below, I'm trying to find a way to get the group matching a pre-defined subset of tokens BUT the group should not contain any tokens outside of the subset. group token 3 37 1 34 1 32…
If multiple entries have the same ID (tag_num), I would like the columns to be the same. For example, if one row may have a description and another one is null but both have the same ID(tag_num), I'd like the…
Below is the query: I got this query over internet while searching to sort comma separated values in a single column SELECT id, title, GROUP_CONCAT(aNumber ORDER BY aNumber) FROM ( SELECT id, title, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers, ',', tens.acnt * 10 + units.acnt…
Considering the case in which we've got two tables, say: SubscriptionPayments and OrderPayments inherit the shared table Payments. Is there a way to find out within the table Payments which table any record belongs to; in other words, is it…
I try to get an amount by joining id and date, but I get an error in the group and the join clause. The expected result is to bring the amount for the max date and lastdate from table 2…
Hi I have some questions regarding table functions in PostgreSQL From https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL;…
I have a category, percentage table in SQL (the table itself has been obtained by the following query: SELECT p.category, sum(p.sales) * 100 / sum(sum(p.sales)) OVER () AS percentage FROM product p GROUP BY p.category; Category Percentage Books 20.51% Clocks…
I have two tables: DOCUMENT and ITEM. Each DOCUMENT has multiple ITEMS, so ITEM has a foreign key to DOCUMENT, let's name it document_id. I want to write a query, to get the total number of documents, that have specific…