Thursday, July 19, 2012

Fetching latest records group by a column - MySQL

I have two tables like this:

Table: tbl1








Table: evt_emp















evt_emp table has tbl1.empid as emp_id and an event_id, when new event is created.
Now, we have to show latest event of each "emp" like this:









Here is the MySQL query for this:

SELECT e1.* , tbl1.name
FROM evt_emp e1
LEFT JOIN evt_emp e2 ON e1.emp_id = e2.emp_id
AND e1.evt_emp_id < e2.evt_emp_id
LEFT JOIN tbl1 ON e1.emp_id = tbl1.empid
WHERE e2.evt_emp_id IS NULL
ORDER BY e1.evt_emp_id DESC;

Do you know any other way? Please suggest.

Here are complete MySQL statements to test / check:

CREATE TABLE IF NOT EXISTS `tbl1` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY (`empid`)
);

INSERT INTO `tbl1` (`empid`, `name`, `city`) VALUES
(1, 'SVU', 'TPT'),
(2, 'SKU', 'ATP'),
(3, 'JNTU', 'HYD');

CREATE TABLE IF NOT EXISTS `evt_emp` (
  `evt_emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  PRIMARY KEY (`evt_emp_id`)
);

INSERT INTO `evt_emp` (`evt_emp_id`, `emp_id`, `event_id`) VALUES
(1, 1, 100),
(2, 1, 121),
(3, 2, 20),
(4, 3, 21),
(5, 1, 150),
(6, 3, 200),
(7, 2, 256),
(8, 2, 258),
(9, 4, 500);

SELECT e1.* , tbl1.name
FROM evt_emp e1
LEFT JOIN evt_emp e2 ON e1.emp_id = e2.emp_id
AND e1.evt_emp_id < e2.evt_emp_id
LEFT JOIN tbl1 ON e1.emp_id = tbl1.empid
WHERE e2.evt_emp_id IS NULL
ORDER BY e1.evt_emp_id DESC;