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] ------------------------------
