[thelist] sql: least/min value

[rudy]

>  SELECT id, file, url FROM ads WHERE state='1'
>        ORDER BY views ASC LIMIT 1
>
> This will only return the first record when ordered by views ascending.
> In other words, the record with the lowest value for views.

hi matt

maybe i never used mysql hands on, but i've seen the documentation, and one
thing i'll say for it, it sure does have a lot of neat functions and
features

anyhow, for those not running mysql with the LIMIT keyword or microsoft
with the TOP keyword, try a subselect to get the same results

   SELECT id, file, url FROM ads WHERE state='1'
        AND  views =
              (SELECT min(views) from ads)

note that if there happens to be an ascending index on the views column,
the subselect should be lightening fast

with ORDER BY the whole table has to be sorted

for personal web sites and other modestly sized databases, you shouldn't
have to worry about performance, but if your table has fifteen million
records, you have to keep in mind that an ORDER BY might take a while...


rudy.ca