### DBI problem on fetchrow

by Win (Novice)
 on Nov 15, 2007 at 17:14 UTC Need Help??
Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

The following bit of code is giving me jip. Please can someone (except marto) point me in the right direction.
```
my \$Select_max_score = "SELECT MAX(M.Score) FROM Regions M ";

my \$sth_max_score = \$dbh->prepare(\$Select_max_score) or die "Couldn'
+t prepare query: ".\$dbh->errstr;

\$sth_max_score->execute() or die "Couldn't execute query: ".\$sth_max
+_score->errstr;

my @max_score;

while (@max_score = \$sth_max_score->fetchrow_array) {
}

Replies are listed 'Best First'.
Re: DBI problem on fetchrow
by moritz (Cardinal) on Nov 15, 2007 at 17:21 UTC
A max(anything) will only return one value (unless combined with GROUP BY or the like).

So you can just

```\$sth_max_score->execute();
# no loop here:
my @max_score = \$sth_max_score->fetchrow_array();
# now \$max_score[0] holds your desired value

There's a prettier solution:

```\$sth->execute();
\$sth->bind_columns(\my \$max);
\$sth->fetch;
# \$max holds the value here
Re: DBI problem on fetchrow
by erroneousBollock (Curate) on Nov 15, 2007 at 17:26 UTC
You've just been given a reasonable answer in the CB.
```  my \$sth = \$dbh->prepare("SELECT MAX(M.Score) as score FROM Regions M
+");
die "can't blah ..." unless \$sth && \$sth->execute();
my \$score = ((\$sth->fetchrow_hashref) || {})->{score};

-David

Re: DBI problem on fetchrow
by kyle (Abbot) on Nov 15, 2007 at 17:27 UTC

This query doesn't look right to me: "SELECT MAX(M.Score) FROM Regions M". Specifically that stray "M" on the end doesn't seem like proper syntax. Since you're specifying "M.Score" as what you want, I'm guessing "M" is a table name. Maybe you want a comma between "Regions" and "M"? It really doesn't die when you try to prepare or execute that?

Update: Oh yes, I have seen table aliases before, thanks for the many reminders. Since I hardly use them myself, I'm not used to seeing them. Sorry about the confusion.

That part of what the OP is doing is ok. "M" is being used as a table alias. SQL syntax supports table aliases either with or without the optional keyword "AS", so these two are the same, they both let you refer to "Regions" as "M":
```    SELECT M.foo FROM Regions AS M;
SELECT M.foo FROM Regions M;

As a matter of style I would recomend using the "as" consistently. It reads better.

BTW, "Regions as M"? Maybe there is a reason to use M for the Regions table, but I would expect R or (in case the table was used several times in the query) something like R_old, R_new, R1, R2, .... In either case I do not see why would you use an alias in a query that only includes one table. I guess a matter of style again :-)

That's called a "table alias" in SQL and is perfectly valid.

You may like to read the SQL99 standard.

-David

The SQL works fine with MySQL, the M is a table alias, the syntax is ... FROM table1 alias1, table2 alias2, ... with the alias\$n being optional

Create A New User
Node Status?
node history
Node Type: perlquestion [id://651025]
Approved by moritz
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2018-03-24 18:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
Voting Booth?
When I think of a mole I think of:

Results (299 votes). Check out past polls.

Notices?