Background Hello all! I recently learned that in newer versions of SQL Server, the query optimizer can “expand” a SQL view and utilize inline performance benefits. This could have some drastic effects going forward on what kinds of database objects I create and why and when I create them, dependin…
Tag: sql
Is it possible to get active sessions per hour in SQL?
start_time end_time HostID gameID 6/14/2021 20:13 6/14/2021 22:22 1 AB1 6/14/2021 20:20 6/14/2021 21:47 2 AB2 6/14/2021 20:22 6/14/2021 22:07 3 AB3 6/14/2021 20:59 6/14/2021 21:15 4 AB4 6/15/2021 21:24 6/15/2021 22:09 1 AB5 6/15/2021 21:24 6/15/2021 21:59 2 AB6 6/15/2021 23:11 6/16/2021 01:22 4 AB7 6/16/2021 …
Laravel get array find collection and delete all
i need to get the id in the form of an array and delete it if they belong to user when trying to delete, I get this error Answer I think you can do it in very simple way i think
How to select column 1 value if exists else column 2 value in PostgresQL?
I want to merge two columns in PostgresQL query by the following rule: select (column_1 or column_2) as column_3 from my_table Is there a way to achieve it? Though quite clear, I want to prefer column_1 value as column_3 but if it is null, I would like column_2 value as column_3. Sorry if this sounds naive, t…
BigQuery correlated subqueries – transform array to array
I’m trying to join array elements in BigQuery but I am getting the following error message: Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN. In my first table I have something like: field1 | field2 …
How to use SUM(CASE WHEN….) in JPQL Hibernate?
I have the following query: However, this throws a syntax error on the query when I put a case statement inside an aggregate. I can get SUM to work fine alone, and case statements to work fine alone. I’m wondering whether JPQL can support cases in aggregates or if I’m forced to use a native query.…
Get users with no postings or no active postings
I have a rails schema and I have users table and postings table. I am trying to get all users that either have no postings or no active postings. Active being a column on postings that can be true or false. Is the following correct? The thing is, if the user has any postings that are active: true, I DO
How to do the equivalent of ‘distinct’ on array field in BQ?
Let’s take the following data: It can also be generated in BQ with the following statement: How would I do the following two ‘distinct’ totals on the right of the following: That is, I want to get a “total” that doesn’t double-count, or rather, only gets the distinct items …
How to make a SQL query partial match with substrings not in the beginning
Let’s say there is a column called: m_report_notation Said column, has values such as: NM_004004.5:c.235del (p.L79Cfs*3) And I would like that, if the value of v is, for example: 235del, then get NM_004004.5:c.235del (p.L79Cfs*3) as a result. This is my current query and what I’ve tried so far (us…
Updating a specified row on a table with information from another table using a trigger and function on PostgreSQL
I have a database for a petshop for class that contains the table Animals with IDs and the dates of the last consult requisition for each animal, amongst other irrelevant columns and the table Requisition which contains the animals’ IDs and requisition dates. And I need to create a trigger that will upd…