RE: SQL Question - apologize for being a bit off-topic

Mike,

Careful, the "min(teacher), min(address), min(state), min(zip)" statement
will provide the lowest of each field, not necessarily the ones matching the
teacher.  You may want to bring in the file a second time on the SQL.  First
to group by school and get the min of the unique key on the file, the second
to look up the key and get the address info.

Try something like this (did not test the statement): 
Select a.school, min(a.teacher), b.address, b.state, b.zip from table a,
table b where a.teacher = b.teacher group by a.school

Regards,
-Greg

Gregory Novak
Manager North American Technical Services
Momentum Utilities Pty Ltd
(630) 985-1736
gnovak@xxxxxxxxxx
www.jacana.com
 
Making Information Work for your Business
www.intuator.com


-----Original Message-----
From: Lisa.Thomas@xxxxxxxxxxx [mailto:Lisa.Thomas@xxxxxxxxxxx] 
Sent: Tuesday, August 02, 2005 7:20 AM
To: mapics-l@xxxxxxxxxxxx
Subject: RE: SQL Question - apologize for being a bit off-topic


Sure.
Select school, min(teacher), min(address), min(state), min(zip) from table
group by school

This of course will assume that all of the addresses are the same for the
school.
-----Original Message-----
From: michael.ellis@xxxxxxxxxxxxxxx [mailto:michael.ellis@xxxxxxxxxxxxxxx] 
Sent: Monday, August 01, 2005 8:08 PM
To: mapics-l@xxxxxxxxxxxx
Subject: SQL Question - apologize for being a bit off-topic

MAPICS Colleagues,

I'm trying to write an SQL and I'm having a brain blockage.  Maybe you can 
help?

I have a list of a group of teachers across the state.   The table 
contains mulitple teachers for each school in the list.

Teacher Name            Address City            ST      ZIP School
John Doe                123 Main        Podunk          IL      66666 
Podunk Middle School
Jane  Deering           123 Main        Podunk          IL      66666 
Podunk Middle School
Joe Teacher             456 Maple       Bucktown        IL      66667 
Washington Elementary School
Sally Teacherette       789 Jefferson   Nowhere IL      66668 Eisenhower 
Middle School


Ok, with such a list, what I want to retrieve, using SQL, is a list of the 
first teacher in every school, along with their associated school name and 
address.
For some reason, I can't figure out how to make this work.   The table is 
on the PC and I'm using Access to perform the query (written with SQL).  I 
haven't tried yet on the AS/400.   I think I need to use the GROUP BY 
function, but then I don't know what else.

Can anyone give me a tip?


Michael G. Ellis
Information Systems International, Inc.
A Global ERP Consulting Firm
815-398-1670 x23
 
Choose ISI consultants to ensure consistently top quality implementation 
services at all levels of your organization.

"The Leader must himself believe that willing obedience always beats 
forced obedience, and that he can get this only by really knowing what 
should be done.......Also he must be ready to suffer more hardships than 
he asks of his soldiers, more fatigue, greater extremes of hot and cold. 
-- Xenophon
_______________________________________________
This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
To post a message email: MAPICS-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mapics-l
or email: MAPICS-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mapics-l.