By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE S(
ID VARCHAR(50),
Type VARCHAR(4),
Quality VARCHAR(4),
FldOrLab VARCHAR(5),
PID VARCHAR(50),
sample_date VARCHAR(10),
sample_time VARCHAR(8),
date_and_time DATETIME
)
INSERT INTO S
VALUES ('Ellis-P5R-Mar2023-3MSD','SD','WQ','Lab','Ellis-P5R-Mar2023-3','03/28/2023','08:16', '03/28/2023 08:16'),
('Ellis-P5R-Mar2023-3MSD','SD','WQ','Lab','Ellis-P5R-Mar2023-3','03/28/2023','09:38','03/28/2023 09:38'),
('Ellis-P5R-Mar2023-3MSD','SD','WQ','Lab','Ellis-P5R-Mar2023-3','03/29/2023','07:46','03/29/2023 07:46'),
('Ellis-P5R-Mar2023-3MSD','SD','WQ','Lab','Ellis-P5R-Mar2023-3','03/29/2023','08:26','03/29/2023 08:26'),
('Ellis-P5R-Mar2023-3MSD','SD','WQ','Lab','Ellis-P5R-Mar2023-3','03/30/2023','08:24','03/30/2023 08:24')
select a.*, (SELECT COUNT(*) FROM S WHERE ID=a.ID and sample_date<=a.sample_date and sample_time<=a.sample_time) as RowOrder
from s as a
select a.*, (SELECT COUNT(*) FROM S WHERE ID=a.ID and date_and_time<=a.date_and_time) as RowOrder
from s as a
| ID | Type | Quality | FldOrLab | PID | sample_date | sample_time | date_and_time | RowOrder |
|---|---|---|---|---|---|---|---|---|
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/28/2023 | 08:16 | 2023-03-28 08:16:00.000 | 1 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/28/2023 | 09:38 | 2023-03-28 09:38:00.000 | 2 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/29/2023 | 07:46 | 2023-03-29 07:46:00.000 | 1 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/29/2023 | 08:26 | 2023-03-29 08:26:00.000 | 3 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/30/2023 | 08:24 | 2023-03-30 08:24:00.000 | 3 |
| ID | Type | Quality | FldOrLab | PID | sample_date | sample_time | date_and_time | RowOrder |
|---|---|---|---|---|---|---|---|---|
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/28/2023 | 08:16 | 2023-03-28 08:16:00.000 | 1 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/28/2023 | 09:38 | 2023-03-28 09:38:00.000 | 2 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/29/2023 | 07:46 | 2023-03-29 07:46:00.000 | 3 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/29/2023 | 08:26 | 2023-03-29 08:26:00.000 | 4 |
| Ellis-P5R-Mar2023-3MSD | SD | WQ | Lab | Ellis-P5R-Mar2023-3 | 03/30/2023 | 08:24 | 2023-03-30 08:24:00.000 | 5 |