MySQL- How to fetch occurrence in MySQL

Question

i have data in MySQL table as

i want to fetch employee records occurrence as shown below

Sample DATA:

CREATE TABLE `login_details` (
  `idlogin_details` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` varchar(45) DEFAULT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`idlogin_details`)
)

INSERT INTO `login_details` VALUES (1,'3','2018-07-11 05:18:59'),(2,'2','2018-07-11 05:18:59'),(3,'4','2018-07-11 05:18:59'),(4,'1','2018-07-11 05:18:59'),(5,'1','2018-07-13 05:18:59'),(6,'2','2018-07-13 05:18:59'),(7,'3','2018-07-12 05:18:59'),(8,'3','2018-07-13 05:18:59'),(9,'4','2018-07-13 05:18:59'),(10,'5','2018-07-13 05:18:59');

Answer 1

I don't know if you intend for that occurrence column to appear in the result set. Assuming you don't, you just need an ORDER BY clause. Assuming you do, then we have to do more work. One simple approach is to use a correlated subquery to find the occurrence number:

SELECT
    idlogin_details,
    emp_id,
    date,
    (SELECT COUNT(*) FROM login_details t2
     WHERE t1.emp_id = t2.emp_id AND t2.date <= t1.date) occurrence
FROM login_details t1
ORDER BY
    emp_id, date;

Demo