I would like to have a question about data contexts on which do the transaction operate with different commands. Are there any differences in SELECT…FOR UPDATE and UPDATE in terms of contexts of data? What I mean by this: if I run the following transaction (pseudo-code): What this actually does is that …
How to use a sub-query to pick out corresponding value for another minimum
I’m trying to find a name of a Community Area that corresponds to the minimum safety score in another column. Current code is resulting table What I want is a table that has COMMUNITY_AREA_NAME defined as WASHINGTON PARK and SAFETY SCORE defined as 1. COMMUNITY_AREA_NAME SAFETY_SCORE WASHINGTON PARK 1 D…
Duplicates in pre-aggregated sub-query sql
I have two tables with many-to-many relationship. I am trying to get values from both of the table where UserId is unique (I’m joining these table on this value) I am rying to use pre aggregated query, but I get error Column ‘clv.ProbabilityAlive’ is invalid in the select list because it is …
Concatenate from rows in SQL server
I want to concatenate from multiple rows Table: |id |Attribute |Value | |——–|————|———| |101 |Manager |Rudolf | |101 |Account |456 | |101 |Code |B | |102 |Manager |Anna | |102 |Cardno |123 | |102 |Code |B | |102 |Code |C | The result I’m looking for i…
Recursive SQL query for finding matches
I have 5 SQL Tables with the following columns: As input data I have employee_id and request_id. I need to figure out whether the employee has access to the request (whether he’s a manager or not) We cannot use ORM here since app’s responsiveness is our priority and the script might be called a lo…
Add hours/minutes to datetime
I’m having an issue on how to add the Offset to the EventDateTime. Is there a way to do this? I would convert it to a decimal number but cannot because 5:30 is not 5.3 hours, it’s 5 hours and 30 minutes. The data is shown below with the expected output. Please help if possible. Sample Data: Expect…
Linking two tables where the same value exists, without Primary key – SQLAlchemy
I have the following tables defined (very simplified version): I am using BigCommerce API and have multiple order_ids in both tables. The order_id is not unique globally but is unique per store. I am trying to work out how to link the two tables. I do have a Store table (shown below) that holds the store.id f…
Presto SQL – Looking to explode Table of IDs to include predefined row numbers
Apologies if this title was a bit confusing. It’s tough to articulate this problem in a sentence. Here is my current table I’d like to explode this table to look like this: Any help would be much appreciated. Answer You want a cross join:
Finding last and second last date and corresponding values
Consider the following schema (fiddle): For each meter.id I need to find the latest reading date, value, and the value difference vs previous reading. For the sample data my output would look like this (plus some other columns from meters): meterid latest value delta value 1 20090403 399 99 2 20090403 288 44 …
MS SQL return single row case dataset without GROUP BY clause
I have a data set of ActivityDate which is a datetime field and EngineHours which is a decimal field. I want to return a single row of data that brings back the average number of EngineHours on each day of the week. The dataset is pretty big so the best performance is likely to be the most ideal, the script