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

[Joel Lieberman]

Rudy, et. al. -

I am starting to think that I have found a great group
of SQL slaves (I am one too)!  This is almost off
topic for the main group - except where it is relevant
to creating web pages.

Stop me if I am off topic.  One thing I noticed in
this particular thread is that the goal of creating an
appropriate  web page is broadly dependent on two
things.  First is composing the correct query for data
from the relational source.  Second is the choice of
tool, and therefore syntax, that is used to manage the
data presentation.  Unfortunately, sometimes the job
is harder with less than optimal tools.

I don't know what kind of data source you have, but
this particular type of report that you have described
becomes trivial when SQL/Plus is used to retrieve
Oracle data.  There is a very convenient parameter
called "BREAK ON"  that lets you choose column(s) to
sub-set for your report (web page).

I use it to produce output like:

Animal
       cat
       dog
vegetable
       carrot
       turnip

where the query is something like   

Select type, animal
from my_table
order by animal;

Since the report is run with BREAK ON type,

The columns sub-set as shown.

Let me know if you wanrt me to continue.


Joel

--- rudy <r937 at interlog.com> wrote:
> > I am afraid you've mistaken my goal, it is not to
> have several times the
> > same logo, but to GROUP all entries featuring the
> aplogo into ONE row.
> 
> ah
> 
> you mean, instead of
> 
>    aplogoA   date1
>    aplogoA   date2
>    aplogoA   date3
>    aplogoB   date7
>    aplogoB   date8
> 
> you want
> 
>    aplogoA   date1  date2  date3
>    aplogoB   date7  date 8
> 
> that's very very hard to with sql
> 
> you need to use the query i gave you (without GROUP
> BY) and "flatten" the
> results (that's a technical term) using php
> 
> 
> > ps: you said: "what GROUP BY does is create one
> row for each
> > value of the group" do you mean, as I think _group
> by_ behaves, it
> > creates one row including all values of the group
> ?
> 
> no, it creates one row for each group and some
> *aggregate* information
> about the group
> 
> using the example data above,
> 
>     select aplogo, count(*)
>       from table
>        group by aplogo
> 
> gives
> 
>     aplogoA  3
>     aplogoB  2
> 
> another example,
> 
>    select aplogo, max(apdate)
>       from table
>        group by aplogo
> 
> gives
> 
>    aplogoA  date3
>    aplogoB  date8
> 
> see?  one row per group
> 
> rudy
> 
> 
> -- 
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt ! 


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/