Group By Order by 1

[trufla]

Hiya!

I am writing a query to group by a particular set of attributes and order by another set of attributes.

What I want is an alphabetical list of species name, but grouped into the species family.

i.e.

Duck
--------
Garganey
Goosander
Woodduck

Swan
-----------
Bewick Swan
Whooper


My query is:-

SELECT DISTINCT SIGHTING.SPECIES#, SPECIES.NAME, SPECIES.FAMILY 
FROM SIGHTING, SPECIES
WHERE SIGHTING.SPECIES# = SPECIES.SPECIES#
GROUP BY SPECIES.FAMILY
ORDER BY SPECIES.NAME;

I get the error:-

ORA-00979: not a GROUP BY expression

Could someone explain this error to me in lamens?
I have had trouble with group by/order by and do not fully understand it.....could anyone explain this to me in lamens also?

Cheers!


Trulfa

 

[PHV]

SELECT SPECIES.FAMILY, SPECIES.NAME
FROM SIGHTING, SPECIES
WHERE SIGHTING.SPECIES# = SPECIES.SPECIES#
GROUP BY SPECIES.FAMILY, SPECIES.NAME
ORDER BY SPECIES.FAMILY, SPECIES.NAME;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[trufla]

PHV I love you!

Can I just ask, (pardon the ignorance) why do I need to specify both attributes in the ORDER BY, GROUP BY?


Trufla

 

[swampBoogie]

As you dont select anything from the sighting table it does not need to be in the from clause which means that the group by is not needed either

SELECT FAMILY, NAME
FROM SPECIES
WHERE SPECIES#
in (select species#
from sighting)
ORDER BY FAMILY, NAME