I have following table abstracting some scenarios and their steps ordered by START_DATE “desc”. SCENARIO_KEY STEP_KEY START_DATE END_DATE PREVIOUS_SCENARIO_KEY PREVIOUS_STEP_KEY 128 44 20xx04x4 0 120 44 120 44 20xx03x3 20xx04x4 120 38 120 38 20xx11x0 20xx03x3 121 38 121 38 20xx07x2 20xx11x0 120 44…
Optimizing MySQL-Query for removing duplicates and adjusting foreign key references
i have following tables:address_table and customer_address_table The address table stores addresses and the customer_address table stores the relation between customer and address. A customer can have multiple addresses, therefore the second table. In the address table there are duplicate rows (different id b…
Detect duplicate JSON nodes in JSON parameter before insert in SQL Server table
I want to detect duplicate nodes from JSON before insert them in SQL Server table. I have this stored procedure that takes a JSON parameter like this: I want to return exception if @SalaryItems has duplicate key How can I detect duplicate in this table in my code? Answer Use OPENJSON() without a with clause s…
How to return deleted rows using jdbi?
How do I return the deleted rows using jdbi? For example, I’d like to do something like this: Answer Postgresql has a returning keyword… https://dbfiddle.uk/?rdbms=postgres_13&fiddle=25d284309745e40c8ea29945481d1aa2 You can then execute that query in the same way as you would a normal SELECT s…
Access VBA string to return a Min & Max date based on matching criteria
I am trying to use a string to get a minimum date from a field (ExpectedTaskStart) in a specified table (tblLiveTasks) using a match on two ID fields (CASEID) from (ExpectedTaskStart) the other (intCaseID) I either get no return or a return of 0. I have used various VBA outlined below but all with same outcom…
select the data of all the students scoring highest marks in each subject group by city
I am using mysql version 8.0.23 the given table is:- and I want to get the complete data of the students having highest marks in each subject from each city — I had used this but still I am not able to see name and id of that student Answer here is one way using window functions:
Grouping by a Range of Numbers with Aggregate Function
I have data in Hive that has two columns of interest. The first is a column (int) that represents a date (YYYYMM) and the second is a column (int) that represents a number of people for that date. I’m trying to write a query that sums up the number of people for comparison across two quarters. For examp…
Track state of time series event in SQL column
This seems really simple but I can’t figure it out, working in SQL Server. I have time series data and I want a column to track the state of ON/OFF events for each row, so when there’s an ON event then the Desired Output column will have a 1 for each subsequent event until there is an OFF event. P…
Oracle Sql- Adding column to pivot result
I’m have an issue that I’m not entirely sure how to fix. So I have a table of the format Where order_type is the column in question I use to pivot. Value is the column I’m using to run aggregates, but the problem is Units. So for each unique ID + order_type combo, units will be the same. But…
How do I select rows that are not matched on specific column (sqlite)
Ok hopefully my table works, I’ve never posted here before, but I’m trying to find who has received doses of more than one type of vaccine from this table (all details are dummy data, not real people or id numbers etc) NHI_id fname lname vaccine_name vac_date 16120428 Clayton Marsh Comirnaty 2021-…