hy folks, i want to make idTime using query like this :
| empID | date | time | idTime |
|---|---|---|---|
| 10001 | 01-07-2022 | 07:30:01 | 1 |
| 10001 | 01-07-2022 | 16:30:01 | 2 |
| 10001 | 02-07-2022 | 07:30:01 | 1 |
| 10001 | 03-07-2022 | 23:30:01 | 1 |
| 10001 | 03-07-2022 | 14:30:01 | 2 |
| 10001 | 03-07-2022 | 24:30:01 | 3 |
| 10002 | 01-07-2022 | 07:30:01 | 1 |
| 10002 | 01-07-2022 | 17:30:01 | 2 |
my query SQL :
SELECT
empId,
dt,
tm,
ROW_NUMBER() OVER (PARTITION BY empId AND dt ORDER BY tm ASC)idTime
FROM
attEmp
order by
empId, dt ASC, tm ASC
but the result from this SQL is wrong. please help me
this db fiddle url : https://www.db-fiddle.com/f/jjoLU7eAzqeN3BqgPam4kz/1
CodePudding user response：
MySQL-8.0 or MariaDB-10.2 is required for Window functions. dbfiddle answer.
PARTITION BY empId AND dt is the erroneous bit. empId and dt is an expression and the output of that expression is what is being filtered.
The correct version is to comma separate this list.
SELECT
NIK,
dt,
tm,
ROW_NUMBER() OVER (PARTITION BY NIK, dt ORDER BY tm ASC)idTime
FROM
attEmp
order by
NIK, dt ASC, tm ASC