Using a self join to select the rows with the maxium values per group
posted: June 1st, 2006 · by: Sven
Phew. This really baffled me. I use SQL for *years* now … but I haven’t been able to come up with a working statement for a query that I’ve thought to be a really trivial thing at first.
Like this:
I have a table articles which can belong to blogs. But blogs are subclasses of an abstract type called location. Thus, each article holds a key to the table locations where there is a column type, which will have the value ‘blog’ for each concrete blog.
Of course there’s more in these tables but here are the relevant columns:
CREATE TABLE article ( id int(10) auto_increment, location_id int(10), modified timestamp ) CREATE TABLE location ( id int(10) auto_increment, type varchar(10) )
Now, I’d like to select all article rows with the newest modified timestamp per blog and sort the results by the modified value. Also I want to exclude just some of the blogs which I already know by their primary key id.
Simple, isn’t it? I was *sure* that it’s possible to just add a GROUP BY clause on the articles location_id, sort them by modified and that’s it. The exclusion of the blogs would be done by a simple, implicit JOIN.
SELECT article.* FROM articles, location WHERE location.id NOT IN (1) GROUP BY article.location_id ORDER BY article.modified
But I got wrong results. I set up two tables with test data because at thought, I’d probably expect the wrong results. I threw 4 articles in the articles table and 3 blogs in the locations table. I.e. one blog had two articles, two blogs had one article.
Again, I got the wrong results. The query above selected the *older* article from the both belonging to the same blog, instead of the newer one. Both of the other articles where present in the results.
What the heck is going on here?
Obviously the ORDER statement applies to the selection *after* the GROUP clause has been applied to the data. Because in the “natural order” of MySQL ISAM Tables older rows will oftenly (not always) be selected first, the GROUP clause first selects the older article row and *then* sorts all selected rows.
That’s been kind of new to me. I’ve always thought it works the other ways around. I asked Google about “group by order by mysql” but found nothing particular illuminating about this.
After some time of playing around with this, looking for alternatives and doing a considerable amount of trial-and-error variations … I’ve yet found nothing and already prepared to throw that stuff aside, go ahead and do an expensive
CREATE TEMPORARY TABLE tmp ... TYPE = HEAP SELECT INTO tmp ... SELECT FROM tmp ...
thing which I wouldn’t like.
More or less by accident I found this page in the (not-really-that-concise) MySQL 3.0/4.0 manual
The docs themselve tell you that something like this is only possible with subselects which the database doesn’t support (MySQL 4.0).
But hidden in the user comments there’s this helpful tip from Csaba Gabor:
“To find the most recent entry (the entry with the highest Id) for each Item, where the Item does not currently have a Deleted status, we could use:”
SELECT t1.* FROM Tracker AS t1 LEFT JOIN Tracker AS t2 ON t1.Item=t2.Item AND t1.Id… which set me on track again.
After some tweaking around, I found this one to be working in my case:
SELECT DISTINCT article.* FROM article, location as l LEFT JOIN article AS a2 ON article.locationID = a2.locationID AND article.modified < a2.modified AND article.locationID = l.id WHERE a2.modified IS NULL AND article.locationID = l.id AND l.type = 'blog' AND l.parentID NOT IN (1)Now, I’ll have to annihilate some pizzas and revel :)
jack said January 24th, 2011 at 03:24 PM ¶
UCVHOST has changed the face of web hosting industry in a major way, people were paying gold for peanuts (and it is still happening). cheap hosting has become synonym with UCVHOST, anybody and everybody who wants a reliable and affordable domain web hosting visits UCVHOST and gets either windows vps or Linux hosting from UCVHOST. UCVHOST sells cheap hosting WITHOUT hidden terms and conditions where as competition has huge MSA and SLA’s which are good enough to confuse a seasoned lawyer also. For clients by now Business with us for the value of windows vps became very critical piece of puzzle for their whole operation, uptime and performance became a huge concern.. However it came with a cost, dedicated servers proved to be at least 100 times expensive in comparison to any windows or Linux plans. Somewhere in the labs engineers were working on splicing raw power of a server into virtual instances, this technology was called as Virtualization also termed as or virtual private servers. Also UCVHOST comes handy when you are looking for remotely hosted and managed FOREX MetaTrader4 terminals. Our forex vps platform is all geared up in fight of pips, our platform support any number of expert advisory (EA) and along with an assure of 100% uptime. Our Virtual Forex Tradng Terminals are well equipped to help you in making money .
QQQ said February 7th, 2011 at 06:33 PM ¶
Finally we kissed and the passion scale went sky high and I knew I was onto a good thing - sex was a certainty free porn videos. She never hesitated when I began to fondle her breasts and she willingly exposed them for me mobile porn. They were firm and I suspected a breast enhancement but said nothing - they still felt good and I was enjoying them and gradually working my way further south free porn tube. She was a step ahead of me and before I could completely undress her she moved on me atk hairy and I was suddenly having my pants pulled down and I was enjoying one of he best cock sucking hairy pussy experiences I had ever had. ABB728019394
chat said March 31st, 2011 at 08:16 PM ¶
The following cleaned up the issue:
Dependencies.loadoncepaths -= Dependencies.loadoncepaths.select{|path| \ path =~ %r(^#{File.dirname(FILE)}) }
side sleeper pillow said April 22nd, 2011 at 07:02 AM ¶
Nicely written article, Knowledgeable and informative post. I’m really glad I came my way along your site. Keep posting, I really like the whole topic. Thanks for sharing.
Okey oyunu said May 12th, 2011 at 04:24 PM ¶
Thanks a lot for this nice post. Tüm dünya artik okey oyunu oynuyor. Yillardir bir çok oyun programi olmasina ragmen, içlerinden en güzeli olarak nitelendirebilecegimiz tek bir site göze çarpmaktadir. Diger tüm okey oyunu programlarinin aksine ücretsiz olmasi ve 3 boyutlu olarak hizmet vermesi mükemmel bir gelismedir. Sizlerde www.okey-oyunu.com adresinden bu essiz okey oyununu indirebilirsiniz. Kullanimi çok basit ve Türkçe dil seçenegi ile kolaylikla oyuna baslayabilirsiniz. Ister kendi ülkenizden, isterseniz dünyanin tüm farkli bölgelerinden dilediginiz oyun odalarini seçerek, oyuna hemen baslayabilirsiniz. Okey oyunu oynamak için artik arkadas bile aramaniza gerek kalmadan, bilgisayarinizdan 100 binlerce üye ile online olarak okey oyununu oynamanin zevkine varabilirsiniz.
porno said May 22nd, 2011 at 01:13 PM ¶
I do agree with all of the ideas you have presented in your post. They’re really convincing and will definitely work. Still, the posts are too short for newbies. Could you please extend them a bit from next time? Thanks for the post.
porno said May 22nd, 2011 at 01:54 PM ¶
good comment. thanks you friends.
I’ve surfed the net more than three hours today, however, I haven’t found such useful information. Thanks a lot, it is really useful to me