returning first row of SQL query

whittick has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have a script (below) and rather than cycle through each row of the table I would like to just return the first row. Is this possible?

my $query="SELECT severity, source, displayname FROM alert WHERE displ
+ayname LIKE '%$displayname%' ORDER BY severity DESC";
my $sth=$dbh->prepare($query);
$sth->execute();
#Loop through for each row returned
while ( @row = $sth->fetchrow_array ) {
    $severity = $row[0];
    $source = $row[1];
    $displayname = $row[2];
    print "$severity\n";
}
[download]

Thanks

Comment on returning first row of SQL query Download Code

Replies are listed 'Best First'.
Re: returning first row of SQL query by moritz (Cardinal) on Mar 29, 2012 at 12:35 UTC
If you just want one row, there's no need to do a `while`-loop at all: `$sth->execute; my ($serverity, $source, $displayname) = $sth->fetchrow_array; $sth->finish; print "severity\n";` [download] Just because the docs use `fetchrow_*` together with `while` doesn't mean you have to do that too. It's good to stick to the patterns from the documentation as long as your use case also matches that of the documentation. Perl 6 - second systems done right	[reply] [d/l] [select]
Re: returning first row of SQL query by Anonymous Monk on Mar 29, 2012 at 12:02 UTC
Is there a reason why you cannot put `LIMIT 1` (or `TOP 1` or whatever your server dialect requires) into your SQL query? Anyway, `$sth->finish` discards the rest of the rows.	[reply] [d/l] [select]
Re^2: returning first row of SQL query by whittick (Acolyte) on Mar 29, 2012 at 12:08 UTC
LIMIT 1 works perfectly, sorry I hadn't thought of solving the problem with SQL rather than through perl!	[reply]
Re: returning first row of SQL query by marto (Cardinal) on Mar 29, 2012 at 12:29 UTC
Unrelated to the problem you're asking about, please read the DBI documentation for Placeholders and bind variables. SQL_injection has more information on the subject.	[reply]
Re: returning first row of SQL query by wallisds (Beadle) on Mar 29, 2012 at 19:22 UTC
`my $query="SELECT severity, source, displayname FROM alert WHERE displayname LIKE '%$displayname%' ORDER BY severity DESC LIMIT 1"; my ($severity, $source, $displayname) = $dbh->selectrow_array($query);` [download]	[reply] [d/l]


go ahead... be a heretic
	PerlMonks