date Value1 Value2 Value3 16-08-2022 a b e 16-08-2022 a b f 16-08-2022 c d f output date Value1_a Value1_c Value2_b Value2_d Value3_e Value3_f 16-08-2022 2 1 2 1 1 2 continues like this for more columns maybe 10, I will aggregate on date and split the categorical columns with counts for each category , curren…
How to calculate rolling timestamp sum from table partitioned by specific column? – SQL
I have a table with a series of timelines that are normalized starting from 00:00:00.00000. I want to summate them sequentially and stitch them together based on my order_key value. Sample Data: Desired Output: My Attempt: Answer Consider below query: Recursive Approach Non-recursive Approach
How to combine two tables in Oracle SQL on a quantitative base
beacause of a really old db design I need some help. This might be quite simple I’m just not seeing the wood for the trees at the moment. TABLE A: ID 1 2 3 4 5 TABLE B: ID VALUE B 1 10 1 20 2 10 2 20 3 10 3 20 3 30 4 10 TABLE C: ID
Modify generated column in Oracle
I have created a database table in Oracle with an auto generated column: Now I do not need it to be generated automatically. I tried to write: But it did not work. The database gives an error: Can someone please explain how to solve this issue without dropping the column? Answer (There’s no such thing a…
How to search for a name in where Oracle with string and number character
I need to find records in Oracle that have only such record like ‘string|number|number|number|number|number’ for example – ‘A12345’ Answer That’s a letter followed by 5 digits:
SQL query to aggregate result of a column comparison
I have a SQL (Postgres) data structure that represents games for a group of matches. Table below shows that there have been 3 matches played (each consisting of either 2 or 3 games). match_id player_one player_two p1_game_result p2_game_result 1 player1 player2 12 10 1 player1 player2 3 11 1 player1 player2 5…
Time Based SQL Injection attack affect all users or just me?
Sorry for asking this silly question but If I found a website which is vulnerable to time base SQL Injection attack. For example I am using (sleep 20) means server will sleep for 20 seconds and then respond me so the server is down for 20 seconds only for me or all the users on website??? Answer Yes, sleep() …
How to get the cumulative sum of children up its parents?
So I have written a query to get the cumulative sum of children but I think partition sum has error as its totalling for the parent that is not part of the children. My fiddle is http://sqlfiddle.com/#!15/88828/1 I have dont a running total but thats wrong. I want the siblings total to a child and child total…
How to group by the amount of values in an array in postgresql
I have a posts table with few columns including a liked_by column which’s type is an int array. As I can’t post the table here I’ll post a single post’s JSON structure which comes as below I’m trying to send posts in the order they are liked (Most Liked Posts). Which should order…
Modifying records in one column of a table conditioning on the date ranges in the other table
I am currently trying to combine Left join with window function (e.g. partition by class_id order by date_to desc) to solve the following problem in SQL. It is quite a difficult problem due to a bunch of tricky constraints that come into play, and it seems to me I need to have a way to choose which rows from …