Wednesday, April 28, 2010

How to limit joined records in MySQL

I have to tables with one-to-many relationship. I needed to select all from the master table and join only one record which contains the oldest date.
The solution is:
SELECT parent.*,
       c1.*
  FROM parent
       JOIN child c1
          ON c1.parent_id = parent.id
       LEFT JOIN child c2
          ON c2.parent_id = c1.parent_id AND c2.sort < c1.sort -- field to determine the order (maybe whatever you want)
GROUP BY parent.id,
         parent.name,
         c1.id,
         c1.name
HAVING COUNT(c2.id) < 1 -- limitation of joined records
ORDER BY parent.name, c1.name;
I found it there.

Saturday, April 3, 2010

ATI driver problems after upgrading Ubuntu 9.10 to 10.04

I have upgraded my desktop Ubuntu 9.10 to 10.04. Everything was fine except ATI driver didn't want to be installed. An error was in fglrx-amdcccle package,After that, I've reported the bug to Ubuntu Bug Tracker, and today I got the decision of my problem:
apt-get remove xorg-driver-fglrx fglrx fglrx-amdcccle
dpkg-divert --remove /usr/lib/xorg/modules/extensions/libdri.so
dpkg-divert --remove /usr/lib/xorg/modules/extensions/libglx.so
apt-get install fglrx

I thank Jean-Baptiste Lallement for help!