I have been working on some database queries and am running into some issues with trying to obtain a primary key for a row where an aggregated value was obtained. I am using Postgresql v10. Take for example the following table: instructor_schedule_id instructor_name course_name start_date 1 bob databases 2015-01-01 00:00:00.000000 +00:00 2 bob databases 2018-01-01 00:00:00.000000 +00:00 3 bob databases
Tag: sql
Extracting a string from a nested dictionary in presto
I have data that looks like this: I’d like to extract “subquestions” into separate rows (in an sql table): — Making ads for Meta platforms — Producing video content — Applying mobile creatives best… I have a tried a few functions json_extract_scalar, and Map functions etc, but I didn’t succeed. Thanks in advance for your help. So the thing I
SUBSELECT (SQL)
I have to tables. The first table (customers) shows data about the customers (id, firstname, lastname). The second table shows data about the orders (timestamp and customer id). So, i wanted to see the last order by a customer in my table “customers” and did a SUBSELECT and it worked. But, there are some customers who never ordered something and
Convert Sql to linq with groupby
I have view on which I use this request And it’s running sucessfully and get me this data How I need to write it using linq to get same data? I tried like this But on new HumpChartDto() {SpendBand = x.SpendBand} I got Cannot resolve symbol ‘SpendBand How I can solve this? Answer First, after grouping on SpendBand, you need
Getting the outer range of a grouped column of range types in Postgres
I have a table with a column name consisting of text values that I want to group by. Additionally I have a table value consisting of numerical range values (of range type). I want to group by the name column and get the outer bounds for each of the groups. Here is an example: Parameter table Wanted output is something
Mysql multiple order, one order by date with condition
I have a simple SQL request which orders the result by the number of sales and then by the number of views. But I would like the new images with a date greater than for example “2022-05-01” to appear just after the ones that have been sold. Is it possible to have multiple order and in one of this order
Oracle Hierarchy – How to get a particular level record in a query?
We have the following structure for Organizations. Query returns the result What I’m trying to do is let’s say I need to get the Department (Information Technology Department) of org_id 4 (Database Unit), how should I build the query for that? Currently we built a function that returns the department id which basically loops until it reaches the DEP parent.
How to find duplicate values according multiple columns and show both
I try to find another examples, but I have not been able to find one that can help me I am currently trying to find if the value in the STR_ROUTE column is in the STR_STREET column, as shown in the following example ID STR_ROUTE STR_STREET 1 MAIN Can 2 AV CAL 3 CLL CLL 4 STR VAL 5 VAL
SQL make 2 columns into 1 from 1 table keep all rows in SELECT query
I am dealing with a DB whose transaction table could have been split up into 2, but dev team decided it was not worth it. Now I want to retrieve the transactions and their dates. Problem is : table includes 2 different types of transactions, meaning: I have 2 date columns : “transaction_date” and “beta_transaction_date” (I am modifying the names
SQL- parse data
I am creating a Test Automation ‘Health Page’ On this page I would like to display a 7 day trend of how many tests ended with status PASS/FAIL/UNDEFINED. I am doing it in Oracle Application Express. I would like the graph to look something like this: However I do not know how to separate the results into days. Here is