SQL query to reduce,delete unnecessary rows

Question

I want to achieve this

ID      | NAME | CODE | TIME                  | Start               | Stop
--------|------|------|-----------------------|---------------------|------------
4B37B930| EV   | FI   | 2017-10-20 08:30:00   | 2017-10-20 08:30:00 | 2017-10-20 08:34:00
3333E8C4| HK   | FI   | 2017-10-20 12:33:00   | 2017-10-20 12:33:00 | 2017-10-20 12:36:00
EF84D06D| HK   | SE   | 2017-10-20 12:37:00   | 2017-10-20 12:37:00 | 2017-10-20 12:39:00

from these rows in the same table

ID      | NAME | CODE | TIME                  | Start     | Stop
--------|------|------|-----------------------|-----------|------------
4B37B930| EV   | FI   | 2017-10-20 08:30:00   |           |
7AA6E032| EV   | FI   | 2017-10-20 08:33:00   |           |
B59D4931| EV   | FI   | 2017-10-20 08:34:00   |           |
3333E8C4| HK   | FI   | 2017-10-20 12:33:00   |           |
1C4890A1| HK   | FI   | 2017-10-20 12:34:00   |           |
AA246243| HK   | FI   | 2017-10-20 12:35:00   |           |
A99DFF3C| HK   | FI   | 2017-10-20 12:36:00   |           |
EF84D06D| HK   | SE   | 2017-10-20 12:37:00   |           |
1D8C4963| HK   | SE   | 2017-10-20 12:38:00   |           |
0358656B| HK   | SE   | 2017-10-20 12:39:00   |           |

That is to delete rows if they are within 5 min and have same NAME and CODE value in the table. Please notice that it is ok for upto 5 consecutive rows to be missing, in this case rows with TIME value 08:31, 08:32 are missing but still I want the first three rows from table 2 to be replaced with one row as shown in table 1 row 1, and similarly, rows 4-7 from table 2 to be replaced with row 2 in table 1 and so on.

Here is a link so SQL fiddle http://sqlfiddle.com/#!9/44af04/1/0

So you want to look up how to use aggregate functions like `min()` and `max()`, plus subqueries or `apply` operators. P.S. Why do you keep `TIME` after getting the `Start` and `Stop` from it? Should it always be the same as `Start`? If so, why keep it? — underscore_d, Dec 07 '17 at 15:37
Possible duplicate of [How to get the MAX(Hour) and The MIN(Hour) for each day in this query?](https://stackoverflow.com/questions/36546178/how-to-get-the-maxhour-and-the-minhour-for-each-day-in-this-query) — underscore_d, Dec 07 '17 at 15:38
How exactly do the IDs relate to one another? Only by time? Your data model doesn't make sense to me, but that's possibly due to the opening post only containing a subset of the data. — SchmitzIT, Dec 07 '17 at 15:41
hello everyone thank you for your efforts, trying to help. Currently there are rows for each minute and when combined with more columns with for example different name values, the end result is huge number of rows, but all I need is continuous time i.e. Start: 08:00 Stop: 22:00 as long as there are not more than 5 consecutive minutes missing — JonDoe, Dec 07 '17 at 15:48
If you want to check the difference between successive rows then you will probably need to add `lag()` or `lead()` into the mix. — underscore_d, Dec 07 '17 at 15:49

ScaisEdge · Accepted Answer · 2017-12-08T07:34:52.900

1

YOu couold use a join on grouped by result

select a.id, b.min_time time, b.min_time Start, max_time Stop
from my_table a
inner join (
select date(time) date, hour(time) hour, min(time) min_time, max(time) max_time
from my_table 
group by date(time), hour(time)
) b on date(a.time) = b.date and hour(a.time) = b.hour

edited Dec 08 '17 at 07:34

answered Dec 07 '17 at 15:43

ScaisEdge

131,976
10
91
107

thanks for your effort, I have added a SQL fiddle link, could you please update the query – JonDoe Dec 07 '17 at 16:32
for what i remember SO is not a coding service .. and however the free coder service operator is not my job .. if my answer is useful you can rate it properly .. but the effort for write your real code should be your job .. – ScaisEdge Dec 07 '17 at 16:38
could you help me fix this error, "Unknown column 'b.time' in 'on clause'", this is updated query "select a.id, b.min_time time, b.min_time Start, max_time Stop from TEST a inner join (select date(time) date, hour(time) hour, min(time) min_time, max(time) max_time from TEST group by date(time), hour(time)) b on date(a.time) = b.date and hour(a.time) = b.time" – JonDoe Dec 08 '17 at 07:32
answer updated .. there was a wrong .. column name – ScaisEdge Dec 08 '17 at 07:35
thanks that works, with some modification "SELECT ID, NAME, CODE, DATE, START, STOP FROM ( SELECT b.ID, b.NAME, b.CODE, b.TIME, DATE(TIME) DATE, HOUR(TIME) HOUR, MIN(TIME) START, MAX(TIME) STOP FROM test AS b GROUP BY b.NAME, b.CODE, DATE(TIME), HOUR(TIME) ) as a", – JonDoe Dec 08 '17 at 11:44

score 0 · Answer 2 · answered Dec 07 '17 at 15:38

0

However your question is a bit confusing to me what exactly you mean by reducing the rows. Guessing it be deletion, you can achieve same by delete query with where clause parameters also with time range less than or greater than operator.

answered Dec 07 '17 at 15:38

Durgesh Kumar

18
4

"less than or greater than" what? And how should the query know which rows within each group are OK to delete? This is a comment, not an answer, as at best, it suggests a vague direction but not a solution. – underscore_d Dec 07 '17 at 15:39
@DurgeshKumar Sorry if it is confusing, by reducing rows I mean to delete rows. for e.g. in above table i want to replace the first 3 rows with 1 row where START is 08:30 and STOP value is 08:34 and the last seven rows should be replaced with one row as shown in the above example table – JonDoe Dec 07 '17 at 15:52
Replace in what sense? Literally change the physical data stored in the table (`update`), or just get a different view of it temporarily (`select`)? – underscore_d Dec 08 '17 at 09:43

SQL query to reduce,delete unnecessary rows

2 Answers2