Re: Why can't use SQL "GROUP BY..."?
On Fri, 2003-01-10 at 00:33, Philip Newton wrote:
> On Thu, 9 Jan 2003 22:59:03 +0800 (CST), [EMAIL PROTECTED] (Gary fung)
> wrote:
>
> > My coding is similar as:
> >
> > $value2 = $dbh->prepare("SELECT page FROM $Table
> >
> > GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
> >
> > Whenever I use "GROUP BY.." , an error statement will go out :
> >
> > "SQL ERROR: Can't find table names in FROM clause!"
>
> Others have pointed out the possibility that $Table may be empty. I'd
> like to add that I think there's another error -- as far as I know,
> GROUP BY can only be used when you have aggregate functions such as SUM,
> MAX, COUNT(...) etc. (For example, "SELECT custno, count(ordernum) FROM
> orders GROUP BY custno ORDER BY 2 DESC" to select the customers together
> with the number of orders, grouped by customer but sorted by number of
> orders.)
>
> Did you mean ORDER BY, perhaps?
Or perhaps "select distinct page from $table"?
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
RE: Why can't use SQL "GROUP BY..."?
Interesting that two people said the exact opposite with respect to performance. As we all know, there are many factors that can influence the performance of a query. If you (Carlos) have to do more multiple full table scans for a distinct, it may be time to tune your db and/or create some indices. It might be more efficient to simply do a SELECT on the fieldname you want the distinct values from and pour it through a small Perl program rather than rely on the DISTINCT clause requiring more than one FTS. Cheers! -Original Message- From: Ronald J Kimball [mailto:[EMAIL PROTECTED]] Sent: January 10, 2003 10:54 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Why can't use SQL "GROUP BY..."? On Fri, Jan 10, 2003 at 10:25:43AM -0500, [EMAIL PROTECTED] wrote: > I just tried this command in Oracle: > select status_flag from status_table group by status_flag > > and it produced the same output as > select distinct status_flag from status_table > > I suspect, though I haven't confirmed this, that the two SQL commands are > optimized to the same strategy by Oracle. I'm not really sure why you would > want to use GROUP BY rather than a DISTINCT clause here. (This is under > Oracle 8.1.7, so YMMV with other databases.) With Oracle 8.1.7, using EXPLAIN PLAN for two queries like the above shows that they are not actually optimized to the same strategy; the former still does a GROUP BY. I agree, it does not make sense to use GROUP BY here instead of DISTINCT/UNIQUE. The two queries will return the same results, but using DISTINCT/UNIQUE will be faster. A similar mistake which I have seen made is to put conditionals in the HAVING clause that should be in the WHERE clause instead. For example: SELECT status, MAX(id) FROM my_table GROUP BY status HAVING status IN (1, 2) should be SELECT status, MAX(id) FROM my_table WHERE status IN (1, 2) GROUP BY status Ronald
Re: Why can't use SQL "GROUP BY..."?
The determining as to which will run faster are a bit too complex to say that one syntax will always run faster than the other. However, using DISTINCT is better SQL and the fact that the query about which this question was asked runs faster using a GROUP BY makes me think that there are partitioning or index issues. I ran similar queries to the 2 Gordon lists below and actually got identical explain plans on them on one table and different plans on another table. One was range partitioned and one was not. The fact remains though, that the GROUP BY clause was not the problem in the original post and if the $Table variable had been assigned a valid value, it would have worked fine. On Fri, 2003-01-10 at 10:54, Ronald J Kimball wrote: > On Fri, Jan 10, 2003 at 10:25:43AM -0500, [EMAIL PROTECTED] wrote: > > I just tried this command in Oracle: > > select status_flag from status_table group by status_flag > > > > and it produced the same output as > > select distinct status_flag from status_table > > > > I suspect, though I haven't confirmed this, that the two SQL commands are > > optimized to the same strategy by Oracle. I'm not really sure why you would > > want to use GROUP BY rather than a DISTINCT clause here. (This is under > > Oracle 8.1.7, so YMMV with other databases.) > > With Oracle 8.1.7, using EXPLAIN PLAN for two queries like the above shows > that they are not actually optimized to the same strategy; the former still > does a GROUP BY. > > I agree, it does not make sense to use GROUP BY here instead of > DISTINCT/UNIQUE. The two queries will return the same results, but using > DISTINCT/UNIQUE will be faster. > > > A similar mistake which I have seen made is to put conditionals in the > HAVING clause that should be in the WHERE clause instead. For example: > > SELECT status, MAX(id) > FROM my_table > GROUP BY status > HAVING status IN (1, 2) > > should be > > SELECT status, MAX(id) > FROM my_table > WHERE status IN (1, 2) > GROUP BY status > > > Ronald -- Jeff Seger <[EMAIL PROTECTED]> Fairchild Semiconductor
Re: Why can't use SQL "GROUP BY..."?
On Fri, Jan 10, 2003 at 10:25:43AM -0500, [EMAIL PROTECTED] wrote: > I just tried this command in Oracle: > select status_flag from status_table group by status_flag > > and it produced the same output as > select distinct status_flag from status_table > > I suspect, though I haven't confirmed this, that the two SQL commands are > optimized to the same strategy by Oracle. I'm not really sure why you would > want to use GROUP BY rather than a DISTINCT clause here. (This is under > Oracle 8.1.7, so YMMV with other databases.) With Oracle 8.1.7, using EXPLAIN PLAN for two queries like the above shows that they are not actually optimized to the same strategy; the former still does a GROUP BY. I agree, it does not make sense to use GROUP BY here instead of DISTINCT/UNIQUE. The two queries will return the same results, but using DISTINCT/UNIQUE will be faster. A similar mistake which I have seen made is to put conditionals in the HAVING clause that should be in the WHERE clause instead. For example: SELECT status, MAX(id) FROM my_table GROUP BY status HAVING status IN (1, 2) should be SELECT status, MAX(id) FROM my_table WHERE status IN (1, 2) GROUP BY status Ronald
RE: Why can't use SQL "GROUP BY..."?
In fact there is a (major) diference!
Using "GROUP BY" we get a faster query results than "DISTINCT",
because with "DISTINCT" we have several full scans on the table.
With "GROUP BY" we only have one full scan getting better performance
results.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: sexta-feira, 10 de Janeiro de 2003 15:26
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Why can't use SQL "GROUP BY..."?
I just tried this command in Oracle:
select status_flag from status_table group by status_flag
and it produced the same output as
select distinct status_flag from status_table
I suspect, though I haven't confirmed this, that the two SQL commands are
optimized to the same strategy by Oracle. I'm not really sure why you would
want to use GROUP BY rather than a DISTINCT clause here. (This is under
Oracle 8.1.7, so YMMV with other databases.)
The error message Gary is receiving is suggesting that $Table is blank.
What is the exact code, Gary? Your message suggests that your code is
similar to the snippet you included. Is that the code verbatim? If not,
could you post a snippet from the actual code.
Cheers!
Gordon Dewis
Production Officer / Agent de production
Geography Division / Division de la géographie
Statistics Canada / Statistique Canada
(613)951-4591
-Original Message-
From: Philip Newton [mailto:[EMAIL PROTECTED]]
Sent: January 10, 2003 00:34
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Why can't use SQL "GROUP BY..."?
On Thu, 9 Jan 2003 22:59:03 +0800 (CST), [EMAIL PROTECTED] (Gary fung)
wrote:
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
Others have pointed out the possibility that $Table may be empty. I'd
like to add that I think there's another error -- as far as I know,
GROUP BY can only be used when you have aggregate functions such as SUM,
MAX, COUNT(...) etc. (For example, "SELECT custno, count(ordernum) FROM
orders GROUP BY custno ORDER BY 2 DESC" to select the customers together
with the number of orders, grouped by customer but sorted by number of
orders.)
Did you mean ORDER BY, perhaps?
Cheers,
Philip
RE: Why can't use SQL "GROUP BY..."?
I just tried this command in Oracle:
select status_flag from status_table group by status_flag
and it produced the same output as
select distinct status_flag from status_table
I suspect, though I haven't confirmed this, that the two SQL commands are
optimized to the same strategy by Oracle. I'm not really sure why you would
want to use GROUP BY rather than a DISTINCT clause here. (This is under
Oracle 8.1.7, so YMMV with other databases.)
The error message Gary is receiving is suggesting that $Table is blank.
What is the exact code, Gary? Your message suggests that your code is
similar to the snippet you included. Is that the code verbatim? If not,
could you post a snippet from the actual code.
Cheers!
Gordon Dewis
Production Officer / Agent de production
Geography Division / Division de la géographie
Statistics Canada / Statistique Canada
(613)951-4591
-Original Message-
From: Philip Newton [mailto:[EMAIL PROTECTED]]
Sent: January 10, 2003 00:34
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Why can't use SQL "GROUP BY..."?
On Thu, 9 Jan 2003 22:59:03 +0800 (CST), [EMAIL PROTECTED] (Gary fung)
wrote:
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
Others have pointed out the possibility that $Table may be empty. I'd
like to add that I think there's another error -- as far as I know,
GROUP BY can only be used when you have aggregate functions such as SUM,
MAX, COUNT(...) etc. (For example, "SELECT custno, count(ordernum) FROM
orders GROUP BY custno ORDER BY 2 DESC" to select the customers together
with the number of orders, grouped by customer but sorted by number of
orders.)
Did you mean ORDER BY, perhaps?
Cheers,
Philip
RE: Why can't use SQL "GROUP BY..."?
I apologize if this has already been covered to but...
Can you run that command by hand and does it work?
Do this to see is both $Table has a value and if the generated code is actaully a
valid sql command.
$query = "SELECT page FROM $Table GROUP BY page";
print " QUERY -$query- \n"
$value2 = $dbh->prepare($query) || die "Couldn't add record, ".$dbh->errstr();
Then paste the query into your program and see if it takes it.
IE
Mysql> SELECT page FROM monkey GROUP BY page;
You could also you use DBI->trace.
Again if this has already been covered sorry, I've been away.
Dan
-Original Message-
From: Philip Newton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 09, 2003 11:34 PM
To: Gary fung
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Why can't use SQL "GROUP BY..."?
On Thu, 9 Jan 2003 22:59:03 +0800 (CST), [EMAIL PROTECTED] (Gary fung)
wrote:
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
Others have pointed out the possibility that $Table may be empty. I'd like to add that
I think there's another error -- as far as I know, GROUP BY can only be used when you
have aggregate functions such as SUM, MAX, COUNT(...) etc. (For example, "SELECT
custno, count(ordernum) FROM orders GROUP BY custno ORDER BY 2 DESC" to select the
customers together with the number of orders, grouped by customer but sorted by number
of
orders.)
Did you mean ORDER BY, perhaps?
Cheers,
Philip
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Why can't use SQL "GROUP BY..."?
Gary Fung a écrit :
>
> Hi,
>
> I have a Perl program using DBI. I don't know why it will cause error when I use
>"GROUP BY..." in SQL "SELECT" statements.
>
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
>
> Can anyboby give help to me?
>
> Thank You very much!!
>
> Regards,
>
> Gary
>
> Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)...
> ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ
You should use $value2 = $dbh->prepare('SELECT page FROM '."$Table".'
GROUP BY page' etc. or use quote function of dbi module
Re: Why can't use SQL "GROUP BY..."?
It might be worth trying:
"SELECT page FROM '$Table' \nGROUP BY page"
and see what results you get.
Joseph
Gary Fung wrote:
> Hi,
>
> I have a Perl program using DBI. I don't know why it will cause error when I use
>"GROUP BY..." in SQL "SELECT" statements.
>
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
>
> Can anyboby give help to me?
>
> Thank You very much!!
>
> Regards,
>
> Gary
>
> Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)...
> ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ
Re: Why can't use SQL "GROUP BY..."?
On Thu, 9 Jan 2003 22:59:03 +0800 (CST), [EMAIL PROTECTED] (Gary fung)
wrote:
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
Others have pointed out the possibility that $Table may be empty. I'd
like to add that I think there's another error -- as far as I know,
GROUP BY can only be used when you have aggregate functions such as SUM,
MAX, COUNT(...) etc. (For example, "SELECT custno, count(ordernum) FROM
orders GROUP BY custno ORDER BY 2 DESC" to select the customers together
with the number of orders, grouped by customer but sorted by number of
orders.)
Did you mean ORDER BY, perhaps?
Cheers,
Philip
Re: Why can't use SQL "GROUP BY..."?
Could be that page does not exist in the table you are trying to query,
or that $Table is not set or calling a non-existant table... Try
printing out $Table by itself before calling the SQL statement.
On Thu, 2003-01-09 at 10:09, Francis Henry wrote:
> Hi Gary:
>
> What happens when you run the SQL statement by itself, outside of the perl script?
>The error seems to lie with the SQL and not
> with perl/DBI. Once you're sure that the SQL statement works by itself, then put it
>into your script.
>
> Regards,
> Francis
>
> Gary Fung wrote:
>
> > Hi,
> >
> > I have a Perl program using DBI. I don't know why it will cause error when I use
>"GROUP BY..." in SQL "SELECT" statements.
> >
> > My coding is similar as:
> >
> > $value2 = $dbh->prepare("SELECT page FROM $Table
> >
> > GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
> >
> > Whenever I use "GROUP BY.." , an error statement will go out :
> >
> > "SQL ERROR: Can't find table names in FROM clause!"
> >
> > Can anyboby give help to me?
> >
> > Thank You very much!!
> >
> > Regards,
> >
> > Gary
> >
> > Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)...
> > ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ
>
> --
> This communication is intended for the addressee(s) and may contain confidential and
>legally privileged information. We do not
> waive confidentiality or privilege by mistransmission. If you have received this
>communication in error, any use,
> dissemination, printing or copying is strictly prohibited; please destroy all
>electronic and paper copies and notify the sender
> immediately.
--
---
Jason H. Frisvold
Backbone Engineer
Penteledata Engineering
[EMAIL PROTECTED]
RedHat Certified - RHCE # 807302349405893
---
"Something mysterious is formed, born in the silent void. Waiting alone
and unmoving, it is at once still and yet in constant motion. It is the
source of all programs. I do not know its name, so I will call it the
Tao of Programming."
signature.asc
Description: This is a digitally signed message part
Re: Why can't use SQL "GROUP BY..."?
Hi,
Is $Table defined before the prepare is executed? I've used group
by in perl without issues. The error returned is complaining about a
missing table name, so I can only conclude that $Table is either blank or
undef when the prepare statement is executed by perl.
Regards,
Michael Nhan
> Date: Thu, 9 Jan 2003 22:59:03 +0800 (CST)
> From: "[big5] Gary Fung" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED], [EMAIL PROTECTED]
> Subject: Why can't use SQL "GROUP BY..."?
>
>
> Hi,
>
> I have a Perl program using DBI. I don't know why it will cause error when I use
>"GROUP BY..." in SQL "SELECT" statements.
>
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
>
> Can anyboby give help to me?
>
> Thank You very much!!
>
> Regards,
>
> Gary
>
> Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)...
> ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ
>
---//---
I don't need to compromise my principles, because they don't have the
slightest bearing on what happens to me anyway.
---Calvin (Calvin & Hobbes).
A computer lets you make more mistakes faster than any invention in human
history - with the possible exceptions of handguns and tequila.
Ratliffe, M.
Re: Why can't use SQL "GROUP BY..."?
Hi Gary:
What happens when you run the SQL statement by itself, outside of the perl script?
The error seems to lie with the SQL and not
with perl/DBI. Once you're sure that the SQL statement works by itself, then put it
into your script.
Regards,
Francis
Gary Fung wrote:
> Hi,
>
> I have a Perl program using DBI. I don't know why it will cause error when I use
>"GROUP BY..." in SQL "SELECT" statements.
>
> My coding is similar as:
>
> $value2 = $dbh->prepare("SELECT page FROM $Table
>
> GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
>
> Whenever I use "GROUP BY.." , an error statement will go out :
>
> "SQL ERROR: Can't find table names in FROM clause!"
>
> Can anyboby give help to me?
>
> Thank You very much!!
>
> Regards,
>
> Gary
>
> Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)...
> ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ
--
This communication is intended for the addressee(s) and may contain confidential and
legally privileged information. We do not
waive confidentiality or privilege by mistransmission. If you have received this
communication in error, any use,
dissemination, printing or copying is strictly prohibited; please destroy all
electronic and paper copies and notify the sender
immediately.
RE: Why can't use SQL "GROUP BY..."?
What DB are you using. Just as a matter of interest what happens if you make
it
"SELECT page,count(*) FROM $Table GROUP BY page"
Also I don't know if this is really the statement you want to execute but
the same effect can be acheived
with
"SELECT DISTINCT page FROM $Table"
Ken.
-Original Message-
From: Gary Fung [mailto:[EMAIL PROTECTED]]
Sent: 09 January 2003 14:59
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Why can't use SQL "GROUP BY..."?
Hi,
I have a Perl program using DBI. I don't know why it will cause error when
I use "GROUP BY..." in SQL "SELECT" statements.
My coding is similar as:
$value2 = $dbh->prepare("SELECT page FROM $Table
GROUP BY page") || die "Couldn't add record, ".$dbh->errstr();
Whenever I use "GROUP BY.." , an error statement will go out :
"SQL ERROR: Can't find table names in FROM clause!"
Can anyboby give help to me?
Thank You very much!!
Regards,
Gary
Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)...
¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ
