[thelist] SQL Query : using Group By and Order By together

[rudy]

> Stop me if I am off topic.

web content management using a database is on topic


>Animal
>       cat
>       dog
>vegetable
>       carrot
>       turnip
>
>where the query is something like
>
>Select type, animal
>from my_table
>order by animal;

surely it's

   order by type, animal

otherwise, carrot would come first

;o)

in my opinion, the creation of break or total lines is probably best done
in the web page script, rather than in sql

that's not to say you can't do it -- see
<http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_c
id424565_tax285649,00.html> for an example of generating total rows using
GROUP BY and UNION ALL

however, where the break or total rows are intermingled with the detail
rows in the result set, it seems to me you'd still want to do some logic in
the web page script in order to display them differently -- for example, to
indent the animals under the type

so even though pl/sql and transact/sql can do break processing on the
database, you still have the problem of those intermingled rows having to
fit the same result set columns, which can get messy if there are any
numeric columns, so you probably have to have logic in the web page script
to handle that anyway, and then you might as well do the break processing
there too

(right now there are a couple guys going "wha?? rudy suggested *not* doing
something in the database??!!")

cold fusion's CFOUTPUT GROUP parameter provides a really elegant way of
handling breaks --

   <cfoutput query="joel" group="type">
      #type#
      <cfoutput>
         #animal#
      </cfoutput>
   </cfoutput>


> Let me know if you want me to continue.

yes, but please trim your replies   ;o)


rudy