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;

Tuesday, July 17, 2012

Yahoo multimessenger trick

Step 1. Click "Start" -> "Run -> type "Regedit" (without quotes) and click "Ok"
Step 2. Click HKEY_CURRENT_USER key -> SOFTWARE -> Yahoo -> Pager -> Test
Step 3. Now right click on right side pane empty area choose "new" -> "DWORD value".
Step 4. Rename it as "Plural" (Without quotes)
Step 5. Now double click it and enter "1" (without quotes) in "Value Data" text box.
Step 6. Close the Registry
Step 7. Restart Yahoo IM (not just logout). Whenever you hit yahoo IM short cut icon, you can get new instance of Yahaoo IM.