OK, thanks for you help, i will write some Perl to sort it then :-)
cheers
Edd
At 09:38 25/01/2002 -0500, Jean-Claude Girard wrote:
>Hi,
>
>In that case I don't see how a select could do what you want. You might want
>to look at doing this through PL/SQL, or sort through the result with Perl.
>
>Jean-Claude
>
>-----Original Message-----
>From: Edd Dawson [mailto:[EMAIL PROTECTED]]
>Sent: January 25, 2002 9:21 AM
>To: Jean-Claude Girard; [EMAIL PROTECTED]
>Subject: RE: SQL query
>
>
>
>Hi,
>
>If i do it with that method it skews the results up, i.e i get a mixture of
>the two records whereas i want just one record, not the max or min values
>from them both together.
>
>Edd
>
>At 09:07 25/01/2002 -0500, Jean-Claude Girard wrote:
> >Teh problem is that when the query returns more than 1 row for a particular
> >student, You have to tell SQL which one you want.
> >
> >You idea of using group by could work, but you will have to use group
> >functions for the other fields.
> >
> >Suppose your query returns something like:
> >
> >Student1 programme1 Faculty1 ....
> >Student1 programme2 Fauclty1 ....
> >
> >You could use
> >SELECT t1.student, MAX(t2.programme), MAX(t3.faculty), ...
> >FROM ...
> >WHERE ...
> >GROUP BY t1.student
> >
> >This would give you 1 row per student, with the associated fields group
> >according to the function you use.
> >Out of the 6 group functions (MAX, MIN, SUM, AVG, COUNT, STDDEV) only MAX,
> >MIN and COUNT may be used on character or date columns. If you want to see
> >an actual value, use MAX or MIN.
> >
> >HTH,
> >
> >Jena-Claude.
> >
> >-----Original Message-----
> >From: Edd Dawson [mailto:[EMAIL PROTECTED]]
> >Sent: January 25, 2002 8:50 AM
> >To: [EMAIL PROTECTED]
> >Subject: SQL query
> >
> >
> >
> >Hi,
> >
> >I have the following query :
> >
> >Select t1.student,
> > t1.college,
> > t2.programme,
> > t3.faculty,
> > t4.forename,
> > t4.surname,
> > t5.number_of_processes,
> > t5.faculty,
> > t5.site_code,
> > t6.context,
> > t6.server_name,
> > t5.username,
> > t6.base_context,
> > t7.server_directory,
> > t2.programme_version
> > from
> > students@live t1,
> > student_modules@live t2,
> > programmes@live t3,
> > people@live t4,
> > cas_usernames t5,
> > cas_faculty_site_servers t6,
> > cas_servers t7
> > where
> > t1.student = t5.student and
> > t2.student = t1.student and
> > t2.record_type = 'PRG' and
> > t2.student_module_status = 'R' and
> > t3.programme = t2.programme and
> > t3.programme_version = t2.programme_version and
> > t4.person_no = t1.person_no and
> > t6.faculty = t5.faculty and
> > t6.site_code = t5.site_code and
> > t6.server_name = t7.server_name
> >
> >i want it to return just one record for every distinct student it picks up
> >regardless of what the rest of the fields contain, i have tried putting
> >distinct around the t1.student column but this has no effect, then i tried
> >to group by t1.student but it complained about the other columns.
> >
> >Surely there must be someway to achieve what i want, but i cant find out
> >how... can anybody help?
> >
> >Thanks
> >
> >Edd
>
>------------------------------
>Edd Dawson
>Senior Analyst Programmer
>University of Derby
>+44 0(1332) 591239
>[EMAIL PROTECTED]
>------------------------------
------------------------------
Edd Dawson
Senior Analyst Programmer
University of Derby
+44 0(1332) 591239
[EMAIL PROTECTED]
------------------------------
