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 came up with the code
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 would use. Here is one
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 | 2020-02-03 | | a | 1 | 4 | 2020-02-03 | |
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 problem. Use a lag()
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 deckId to
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 because it would ignore either the product 1
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