Justtechjobs.com Find a programming school near you






Online Campus Both


php3-list | 2000051

Re: [PHP3] SQL: count / limited group? From: Doug Semig (dougslist <email protected>)
Date: 05/15/00

At first I thought you only wanted the last 100 in the query that you were
already doing, but upon re-reading your question I now feel you're asking
how to limit a query so it will only operate on the last 100 rows in a table.

Is this the situation? You have a table into which rows keep getting
inserted. You want to run this SQL that you're showing us on _only_ the
last 100 rows that have been inserted into the table.

If this is the case, you'll need something in the table to use to order the
rows in reverse-inserted order. For example, a timestamp of when the row
was added to the table or even a sequential key value would work. If you
have either of those, the way to do it without subselects is this:

  1. Select the fields you want from the table into a temporary table,
putting them in descending order by the timestamp field or the sequential
key value and limiting the output to 100 records

  2. Run the SQL you've shown us on the temporary table and display the
results on your web page

  3. Drop the temporary table (if it's not done automatically)

But even if you had the use of subselects, you would need some way to grab
only the last 100 rows of a table. Since SQL does not guarantee the order
in which rows will be returned for a query without an ORDER BY clause, you
**must** have something to order your rows in descending order of which
they were inserted so you can get only the 100 records you're interested
in. That's why a timestamp value or a sequentially incremented counter
would be good. Once you've gotten the exact 100 rows you're interested in,
you can run the query you've shown us.

Note that a timestamp value may be better because if a sequentially
incremented counter may limit the number of rows in your table to the
maximum number in the series. The maximum is usually over 2 billion,
though, so at least it's not that bad. Also, if you can set your
sequentially incremented counter to roll over back to the first number in
the series, and it ever reached the maximum number in the series, it would
break any get-the-last-100-inserted-rows processing based upon it. But use
whichever makes sense to your app.

Good luck,
Doug

Dieter Kneffel was heard at 03:14 AM 5/16/00 +0200 to say:
>currently, I am using this query to get the most used names in a
>complete (MySQL) database.
>
>$select = "select COUNT(name) as top ,name from table group by name
>order by top desc";
>
>--
>
>here's the task: I only want to get the most used names from e.g. the
>last 100 rows.
>(and just ignore the other, older entries in the db)
>
>how do I have to modify the above select? Is it possible in one query,
>do I have
>to use sub-selects (could be a problem with mysql?!), or what other
>options do I have?
>
>tnx,
>
>dieter 

-- 
PHP 3 Mailing List <http://www.php.net/>
To unsubscribe, send an empty message to php3-unsubscribe <email protected>
To subscribe to the digest, e-mail: php3-digest-subscribe <email protected>
To search the mailing list archive, go to: http://www.php.net/mailsearch.php3
To contact the list administrators, e-mail: php-list-admin <email protected>