I’d like to rework a script I’ve been given. The way it currently works is via a WITH CTE using a large number of UNIONs. Current setup We’re taking one record from a source table, inserting it into a destination table once with [Name] A then inserting it again with [Name] B. Essentially cre…
What is the best way to get a derived status column based on existing result
I have a table : For a test there can be multiple run/execution. Each run have a result. here for result column, 0 is fail and 1 is pass. I want to query –if all the run PASS for test, the OverallStatus is PASS –If all the run Faile for a test, the OverallStatus is FAIL –If some of them
PostgreSQL(function) – Implementation of where clause to get rows by a value(concatenate) in the JSONB column
I am trying to get the rows from a table where I want the condition to check for a value from a jsonb column. The column stores the data as: In the function, I check for the value using: but I want the 420 to be replaced with “ID” which I pass through the function. The only way I came
SQL statement to Create Role fails on Postgres 12 using Dapper
I am running Postgres 12 on Windows and have a .Net Core app which uses Dapper as an ORM: The following query works fine: Now I’m trying to execute an sql statement that would create a role: This query fails with the following exception: Npgsql.PostgresException: ‘42601: syntax error at or near &#…
Group by one Column in Sql Server and Order by Another Column not contained in either an aggregate function or the GROUP BY clause
i have table “ApplicationEventsSearch” with two column Keyword and EventDate i want to have a query that return result as distinct keyword , but ordered asc by EventDate i have tried many combination , but none worked Data Desired Result and what i have tried so far Answer Aren’t you after j…
selecting max value from table with two variable colums (microsoft SQL)
i´m working with a table that looks like this: Start https://i.stack.imgur.com/uibc3.png My desired result would look like this: Result https://i.stack.imgur.com/v0sic.png So i´m triyng to select the max value from two “combined” colums. If the values are the same amount (Part C), the outcome does…
Spring Data JpaRepository “JOIN FETCH” returns duplicates
I’m writing a simple Spring Data JPA application. I use MySQL database. There are two simple tables: Department Employee Each employee works in some department (Employee.department_id). The method getAll returns a list with duplicated departments (each department is repeated as many times as there are e…
How update row with inner joins
I have this select statement where I found two rows, and I want to update a specific column: I have tried: How do I correctly construct the update query? SOLUTION: Answer Just put your query as a table: Here is a demo: DEMO
SQL How to avoid groupby
I have this query that it results is not correct and I assume it groupby issue Fiddle This is my dbfiddle Query Result Expected In reality only user1 has done 4 requests Answer Your result shows one registry with ‘Title1’. Per registry you can have many users (in table RegistryMember). And per reg…
Unique combination of records in a SQL table (Get edges from nodes table)
I came across a use case where I wanted to generate a combination table where the attributes of a record had to be matched with all other records in the table. Something like creating list of edges in a Complete Graph using a nodes table. To give you an example of the requirement, you can consider the followi…