[an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] (none) [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] (none) [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive]
 
[an error occurred while processing this directive] [an error occurred while processing this directive]
Skåne Sjælland Linux User Group - http://www.sslug.dk Home   Subscribe   Mail Archive   Forum   Calendar   Search
MhonArc Date: [Date Prev] [Date Index] [Date Next]   Thread: [Date Prev] [Thread Index] [Date Next]   MhonArc
 

Re: [PROGRAMMERING] [SQL] group by hjælp



On Sat, 2 Jun 2007 19:26:32 +0000 (UTC)
Jesper Krogh <sslug@sslug> wrote:

> I sslug.programmering, skrev Martin Møller Skarbiniks Pedersen:
> > > >  mysql> select id,bogtitel,salgssted,min(pris) from t2 group by
> > > >  mysql> bogtitel;
> > 
> >  Er det overhovedet lovligt SQL ? Det virker ihvertfalde ikke på en
> >  Oracle 8 (hvilket også er gammel).
> > 
> > 
> >  SQL> select id,bogtitel,salgssted,min(pris) from t2 group by
> >  SQL> bogtitel;
> >  select id,bogtitel,salgssted,min(pris) from t2 group by bogtitel
> >         *
> >  ERROR at line 1:
> >  ORA-00979: not a GROUP BY expression
> 
> Og heller ikke i postgresql.. som sagt .. en bug i mysql. 
> 

NEJ! en alternativ implementation. Ifølge de seneste  SQL standarder
(både ISO og ANSI) er der ikke mere krav om at alle SELECT kolonner er
aggregerede, hvilket er den mere primitive udgave som f.eks. Oracle og
Postgresql implementerer. 
Kig f.eks. i SQL standarden af 1999 eller 2003 sektion 7.12-15 og du
vil se: 
----
If T is a grouped table, then let G be the set of grouping columns
of T. In each <value expression> contained in <select list>, each
column reference that references a column of T shall reference some
column C that is functionally dependent on G or shall be contained in
an aggregated argument of a <set function specification> whose
aggregation query is QS.
---
Det kræver så en smule indsigt i konceptet "functionally dependant" for
at kunne se at dette faktisk åbner op for queries som denne.
 At resultatet af denne query så ingen mening giver er en anden sag.
Ca samme resultat vil kunne opnåes ved at skrive salgssted i "group by"
listen. 
Hvis man ikke kan lide at MySQL ikke giver en fejlmeddelelse om at
resultatet ikke nødvendigvis giver mening, så kan man altid smide
ONLY_FULL_GROUP_BY i ens sql_mode setting. 




-- 
    Kim Schulz       | Blog: http://www.schulz.dk/
Email: sslug@sslug | Work: http://www.devteam.dk/
Phone: +45 5190 4262 | Fun : http://www.chilifan.dk/


 
Home   Subscribe   Mail Archive   Index   Calendar   Search

 
 
Questions about the web-pages to <www_admin>. Last modified 2007-07-01, 02:01 CEST [an error occurred while processing this directive]
This page is maintained by [an error occurred while processing this directive]MHonArc [an error occurred while processing this directive] # [an error occurred while processing this directive] *