Select Query to create a new field and populate

My query

CREATE TABLE quad2017_1
select distinct
`a`.`event_id` AS `event_id`,
`a`.`user_ID` AS `user_ID`,
`a`.`date` AS `date`,`a`.`Time` AS `Time`,
`a`.`Noise` AS `Noise`,`a`.`Signal` AS `Signal`
from (`quadrantids2017` `a` join `quadrantids2017` `b`)
where ((`a`.`date` = `b`.`date`)
and (`a`.`user_ID` <> `b`.`user_ID`)
and ((time_to_sec(`a`.`Time`) - time_to_sec(`b`.`Time`)) between -(1) and 1))
order by `a`.`date`,`a`.`Time`,`a`.`user_ID`

produces an output like so (event_id, user_id,date,time)

37776 2 2017-01-01 01:08:45
47827 3 2017-01-01 01:09:07

33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00

33503 1 2017-01-01 01:24:48
47829 3 2017-01-01 01:24:49
37781 2 2017-01-01 01:24:53

Is there a way that I can get to to produce this
(event_id, user_id,date,time) and populate a new field called group

37776 2 2017-01-01 01:08:45 1
47827 3 2017-01-01 01:09:07 1

33501 1 2017-01-01 01:14:59 2
47828 3 2017-01-01 01:15:00 2

33503 1 2017-01-01 01:24:48 3
47829 3 2017-01-01 01:24:49 3
37781 2 2017-01-01 01:24:53 3

that way when I display on line I can display in groups

Regards
John B

 

Well, no. Not unless you can get those values from somewhere. How is the group-number decided? There seems to be no correlation between IDs / time / userID and group-id.
Normally, you would store either the group directly into the table, or you could have another table with group-information, and then link the two on SELECT via a join. Currently, it seems your DB-setup isn't really up to scratch.

 

Sorry I was probably not being clear

the query returned these records and the relationship is the time as in they are events that occur within a second of each other, so the query works and creates a table for the results, somehow I wondered if I could some type of marker to enable me to relate the results so for instance the first 2 are related but without some form of marker once in a table if they were not sorted by date/time I would not know - which event was related to which event - does that make sense ?

37776 2 2017-01-01 01:08:45
47827 3 2017-01-01 01:08:46

33501 1 2017-01-01 01:14:59
47828 3 2017-01-01 01:15:00

33503 1 2017-01-01 01:24:48
47829 3 2017-01-01 01:24:49
37781 2 2017-01-01 01:24:49

John B

 

Hello,
If you are working on SQL Server, following script would work without a problem.
On other databases systems, if you have Lag() function then it will work again.
Please keep in mind that CTE expressions might be required to converted into SubSelect statements

;with cte as (
select
    *,
    rn = ROW_NUMBER() over (order by date, time),
    lag(date,1,null) over (order by date, time) lagDate,
    lag(time,1,null) over (order by date, time) lagTime
from myData
), cte2 as (
select *, DATEDIFF(mi,lagTime,time) SSDiff,
    case
        when rn = 1 then 1
        when (Date = lagDate) and (DATEDIFF(mi,lagTime,time) <= 1) then 0
        else 1
    end as increment
from cte
)
select *, grp = (select sum(t.increment) from cte2 t where cte2.rn >= t.rn)
from cte2

Code (SQL):

for a very similar case you can refer to group table data using SQL Lag function example http://www.kodyaz.com/t-sql/sqlserver-lag-function-to-group-rows-on-column-value.aspx

Output is as follows



I hope it helps