Re: [PHP-DB] An SQL-query puzzle... SELECT max 2 rows from tables having id 'a' or id ' b' or id 'c'... From: Doug Semig (dougslist <email protected>)
Date: 11/20/00

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 wasn`t right... But maybe it`ll help you understand... :)
>
>From SQL puzzler! 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>