I have a SQL table with failed testresults: This table is telling me that the test has failed, but I want to know if it is the first, second or third test. In the end I would like to do it with a logo where a x stands for a failed test, and a X for the concerned test, like
Tag: window-functions
How to find duplicate records in MySQL, but with a degree of variance?
Assume I have the following table structure and data: I’d like to be able to find duplicate transactions where the description and amounts match, but the date would have some degree of variance +/- 3 days from each other. Because the “Burger King” transactions are within three days of each other (2020-08-20 and 2020-08-23), they would be counted as duplicates,
SQL query grouping by range
Hi have a table A with the following data: I’d like to have the following result: My DB is DB2/400. I have tried with ROW_NUMBER partitioning, subqueries but I can’t figure out how to solve this. Answer I understand this as a gaps-and-island problem, where you want to group together adjacent rows that have the same PA. Here is an
How to order occurrences counting from 1 to n occurrences
I have a table that contains several columns, but for the task, the important ones are id end created_date, where id can repeat. Sample data: +——–+————–+ | id | created_date | +——…
Add value of type B to type A of same org_id
I have a table like this Now I want to select values from this table for each org_id, type but for type A i want to add value of type B of same org_id to type A. My query should return this In this case, when ORG_ID is 1 and TYPE is ‘A’, I have to add that organization’s type
SQL Spark – Lag vs first row by Group
I’m SQL newbie and I’m trying to calculate difference between the averages. I want for each item and year calculate difference between months, but I want always substract current average – fist month …
how to update column with row number in group by result in MySQL
Sample Data: Desire Data: how to write a sql to update the room seat number to serial num in MySQL 5.7? the room’s seat is from 2-20. Answer One option uses the update/join syntax. In MySQL 5.7, where window functions are not available, you can emulate row_number() with a correlated subquery (which is somehow safer than user variables): Demo on
How to find whether the same customers who ordered this month also ordered the next month?
I have an orders table Now I want to find how many of the users ordered in 1st month also ordered in the next month. In this case, user_id’s 32,24,27 ordered in 7th month but only 24 and 32 ordered in the next month. I want the result to be like : I’m lost here. Can someone please help me
How to group data if difference between rows is mothe than 1 minute
Im trying to create a new table on SQL which will have summarized data. For example. In the original table i have this data: If you see rows 9, 10 and 11 they are consecutive, so i want to group them …
Rank based on cumulative value
I want to rank on ID and value columns based on ascending order of UID. Expected output has to change once value column has a different value than the previous value. Ranks has to restart on each new ID Here is a sample dataset that I have created: Answer I think that the simplest approach to this gaps-and-islands problem is