Re: An SQL-query puzzle... SELECT max 2 rows from tables having id 'a' or id ' b' or id 'c'...

Whenever you're using SQL to do non-set operations, it's going to get hairy or impossible (within SQL only), Trond. If I'm reading your requirements correctly, there's a common workaround you might want to consider, though. Add a column to your article table called something like "active" or "frontpage". Either within your code that inserts articles or as a trigger when inserting into the article table, you have to make sure that only the last two articles inserted for that place have a value in this new column. All other articles for that place have a NULL in the new column. As a query optimization technique, build an index on this column if your RDBMS will not index NULL values. Then you just SELECT the records with active = 'Y' in the WHERE clause. The idea is that you can afford some overhead while inserting articles because the primary function isn't inserting articles, but rather reading articles. The only other ways to do what you want are to hard-code a huge WHERE clause like you have already mentioned, or run multiple queries against the db that basically first grabs all the portalids from the newsportal table then iterates through them SELECTing out only two records. But this multiple query "solution" is possible because you're using a procedural language (PHP), which will allow you to pound the database with as many queries as you deem appropriate and do whatever you like with the results. A couple of other things: "date" is an SQL reserved word, and I hope you're not really storing the articleid in your newsportal table as implied by your example "WHERE article.articleid = newsportal.articleid AND ...". I would think a WHERE clause that starts with something like "WHERE article.portalid = newsportal.portalid AND ..." would be more along the lines of what you meant. If you have it the way you implied, I think your relation is the wrong way 'round. ----A little more about the workaround---- newsportal (portalid int, name varchar(32)) article (articleid int, portalid int, dateadded date, active char(1), story varchar(255)) When inserting an article, you have to make sure that only two articles are marked "active" for the portalid that you're inserting the article for. This can easily be done in PHP by first selecting out the two that are already marked active, deciding which one is to be dropped off, UPDATE that record to set its "active" column to NULL, and insert the new article with "active" as 'Y' or whatever you like. Yes, this is overhead on the insert...but it makes it very easy on the database when you're selecting. If your database is mostly read from, and not inserted into very often, this is the way to go. To select out the records, "SELECT name, dateadded, story FROM newsportal, article WHERE active = 'Y' ORDER BY name ASC, dateadded DESC" ought to do it. That's why this workaround is used...it's very easy to get the data out of the database. There are limitations to this workaround...for example, if you ever want to change the number of articles you display on your "front page", then you have to update your insert code (or trigger). That's not very "database-like." But I've seen it used quite often because the alternative of pounding the database with SELECTs for every classification (in your case, portal) is much more database-like, but less than efficient. Your suggested alternative is only slightly less efficient than this workaround, and I've seen it too...but it makes for wicked WHERE clauses that have to be updated every time you add a classification (in your case, portal). Doug At 12:58 PM 11/20/00 +0100, Trond Erling Hundal wrote: >Hey hey hey... >I`ve stumbled across a little SQL puzzle here... maybe some of you can >help... >I am building a "main page" for many "sub main pages" >The idea is to have several pages displaying newsflashes for their specific >subject... >Example: >Main page 1 only contains news about a place called Grewin >Main page 2 only contains news about a place called Orklan >Main page 3 only contains news about a place called Lillo >Main page 4 only contains news about a place called Dogmax > > > >The news for these sites have already been stored in a MySQL table called >article. >The relation between the article and the specific site is stored in a table >calles newsportal. >NOW, I want to select the 2 top stories from each of these portals, to >display them on a "main-main page"... >So the main page displayes the two newest articles from page 1, 2, 3 and 4. >A total of eight articles in this case... > >Maybe this is really easy, or maybe I have to reconstruct the db.... >I`m using MySQL.... > >Any ideas will be GREATLY appreciated! > >-------------------------- >Maybe something like this: >SELECT article.story as story, article.date as date FROM article, newsportal >WHERE article.articleid = newsportal.articleid AND newsportal.portalid = 1 >OR newsportal.portalid = 2 OR >newsportal.portalid = 3 OR newsportal.portalid = 4 ORDER BY date DESC LIMIT >0, 8 >---- no , that wasnt right... But maybe itll help you >understand... :) > >From SQL puzzler!