Hi folks,

I have a db which is similar to the example

Name | Country | Type
test | USA | Nm
test2 | UK | Nb
test3 | USA | Nc
test4 | USA | Nm

What I want to know is if there is a simple SQL statement to do the
following:

1) Select all entries where country = USA (for example)
2) Group by Type
3) Display each group under each heading Type - i.e.

Nm
test | USA
test4 | USA

Nc
test3 | USA

I'm going to keep looking, but I want to keep my SQL to a minimum and
wondered what the official way of doing this would be.

Thanks

A

UKuser wrote:

> Hi folks,
>
> I have a db which is similar to the example
>
> Name | Country | Type
> test | USA | Nm
> test2 | UK | Nb
> test3 | USA | Nc
> test4 | USA | Nm
>
> What I want to know is if there is a simple SQL statement to do the
> following:
>
> 1) Select all entries where country = USA (for example)
> 2) Group by Type
> 3) Display each group under each heading Type - i.e.
>
> Nm
> test | USA
> test4 | USA
>
> Nc
> test3 | USA
>
> I'm going to keep looking, but I want to keep my SQL to a minimum and
> wondered what the official way of doing this would be.
>
> Thanks
>
> A

Item 3 is the tricky one, although it would be easy with a bit of php
(or similar, presumably) provided that's available to you.

Anyway, here's the query:

SELECT type,name,country FROM table WHERE country = 'USA' GROUP BY type
ORDER BY type

Hi,

Thanks for the SQL. It was point 3 which I wanted to see if it could be
done in the same SQL statement or if it was separate.

A


strawberry wrote:
> UKuser wrote:
>
> > Hi folks,
> >
> > I have a db which is similar to the example
> >
> > Name | Country | Type
> > test | USA | Nm
> > test2 | UK | Nb
> > test3 | USA | Nc
> > test4 | USA | Nm
> >
> > What I want to know is if there is a simple SQL statement to do the
> > following:
> >
> > 1) Select all entries where country = USA (for example)
> > 2) Group by Type
> > 3) Display each group under each heading Type - i.e.
> >
> > Nm
> > test | USA
> > test4 | USA
> >
> > Nc
> > test3 | USA
> >
> > I'm going to keep looking, but I want to keep my SQL to a minimum and
> > wondered what the official way of doing this would be.
> >
> > Thanks
> >
> > A
>
> Item 3 is the tricky one, although it would be easy with a bit of php
> (or similar, presumably) provided that's available to you.
>
> Anyway, here's the query:
>
> SELECT type,name,country FROM table WHERE country = 'USA' GROUP BY type
> ORDER BY type

UKuser wrote:
> Hi,
>
> Thanks for the SQL. It was point 3 which I wanted to see if it could be
> done in the same SQL statement or if it was separate.
>

It _can_ be done, with generated SQL in stored procedures and temporary
tables, but it's usually not worth the overhead of writing,
maintaining, and securing it. I've never tried to execute generated SQL
in a MySQL stored procedure, so I'm not even sure it has a function to
do so.

--Thomas

UKuser wrote:

> What I want to know is if there is a simple SQL statement to do the
> following:
>
> 1) Select all entries where country = USA (for example)
> 2) Group by Type
> 3) Display each group under each heading Type - i.e.


I always get confused when folks talk about SQL results as what
is "displayed".
I think more in set-based logic, or how it's structured.

What do you mean when you say "Display each group under each
heading"?

strawberry wrote:

> Anyway, here's the query:
>
> SELECT type,name,country FROM table WHERE country = 'USA' GROUP BY type
> ORDER BY type

I get all muddle-headed with this whole GROUP BY thing.
What does the record of a GROUP BY query look like? Does it
have a special column, or go beyond just two-dimensional tables?

Hi Folks,

Thanks for your advice. I've solved the problem by doing the following:

1) SQL(1) to select distinct headers from database
2) Print SQL(1)->headers
3) Execute a second (sub) SQL statement SQL(2) *foreach* SQL(1)
4) Print SQL(2)->name (i.e. unique records)
5) SQL(2)->MoveNext();
6) SQL(1)->MoveNext();

I'm using ADODB and can HIGHLY recommend it as I'm working inhouse with
Access and live will be MySQL and can change with a very simple coding
switch. Its probably not pushing the db to its limits (i.e.
transactions and stuff which could be restricted by ADODB) but for
small-medium dbs (and probably some larger ones) is great.

Anyway - hope my thoughts help. I've not posted the code as its ADODB
and probably irrelevant for most of you.

Cheers

A

Sanders Kaufman wrote:

> strawberry wrote:
>
> > Anyway, here's the query:
> >
> > SELECT type,name,country FROM table WHERE country = 'USA' GROUP BY type
> > ORDER BY type
>
> I get all muddle-headed with this whole GROUP BY thing.
> What does the record of a GROUP BY query look like? Does it
> have a special column, or go beyond just two-dimensional tables?

tgmayfield wrote:
> I've never tried to execute generated SQL
> in a MySQL stored procedure, so I'm not even sure it has a function to
> do so.

FYI:
http://dev.mysql.com/doc/refman/5.0/en/sqlps.html


Regards,
Bill K.