Here is my MySQL table. I updated the question by adding an ‘id’ column to it (as instructed in the comments by others). I want to add a new column called row_num to assign an incrementing number ONLY for duplicates, as shown below. Order of the results does not matter. I followed this answer and …
Tag: window-functions
Finding cumulative sum using SQL Server with ORDER BY
Trying to calculate a cumulative sum up to a given number. Need to order by 2 columns : Delivery, Date. Query: Results: Expected results: Version: Microsoft SQL Server 2017 Answer You need a third column in the ORDER BY clause to break the ties on Contract_Date and Delivery. It is not obvious which one you wo…
Dense rank is not generating rows correctly
I have a table A: I have inserted 11 rows. + —–+——–+—–+————+ | NAME | NUMBER | EXC | D1 | + —–+——–+—–+————+ | a | 1 | 1 | 2020-02-03 | | a | 1 | 2 | 2020-02-03 | | a | 1 | 3 | 20…
SQL – Use dense_rank and group by together
I was under the impression that when we use rank/row_number/dense_rank, we can NOT use group by, but why does below logic run successfully Select product, type, dense_rank() over (partition by type …
Date cycle in T-SQL
I need to write a query that displays time intervals that go on without interruption. Example: Input: Output: I think we need to use the function DATEDIFF line by line. The problem is that I don’t know how to access indexes. Or can this problem be solved much easier? Answer This is a type of gaps-and-islands …
MySQL Get Change From Cumulative Results in Consecutive Rows by Identifier
I am running MySQL Community Server version 8.0.19. I have been struggling with the following problem whilst working through publicly available COVID19 data. I am using a dataset that is both …
SQLite 3.24 – Counting occurrences of value grouped by id without using window functions
Apologies if this has been asked, but maybe I’m not familiar with the language to ask for what I want, and I looked through dozens of other questions but don’t seem to be finding what works. I’m working on a tool for a trading card game, a deck is a list of cards, I have a table mapping deck…
SQL clean History repeated intermediate values
I’ve noticed that my table has multiple redundant values that should be cleaned, it’s a table that records price changes, so I would like to clean the following way: To this: Also assume that in this case the column id is the same as date. SELECT DISTINCT ON (product, price) won’t work becau…
Count number of students above and below the average score in SQL
I have a sample table below and I am trying to get the number of student above the average score and number of students below the average score. The target result table is supposed to look like I have been able to write a query to get the names of students above the average score and this can be easily
oracle sql, id’s, group by one column, mulitple distinct in other column
I am trying to figure out how to write a query to do this. I have two ID’s in the table ID1 and ID2. Below is what I want. Want ID2 has count(ID2)>1 count of distinct ID1 >1 I am not sure how to do this with Oracle SQL. I did write code to do the group by. However, I