Hi,

I wouldn't normally post without looking through the forum for similar questions but I really don't know how to formulate this in a search. So, please if you can and you find time, help me.

I am trying to pick out graduated students within a certain calendar year. It's a rather long query, involves some joins on different tables and I have the following problem:

I have a table of all graduated students within the last say 10 years. Every student has a unique id as primary key and there are students that have graduated with more than one degree from this institution. Every degree receives a (global) sequence number. I need to pick the students with only one degree for this term, i.e. student A could have graduated with a both BA and a BS in the calendar year in question. They would then be marked number 1 (BA) and number 2 (BS) and I only need number 1. So technically, I could do a search by sequence = 1 and be done with it. The problem arises when student B graduated with his 1st degree a couple of years back and is in my graduate population now with a degree marked 2nd that I need to pick it up. The sequence number then becomes useless.

My question is, is it possible to while selecting multiple rows to do a comparison between the retrieved results and pick up the one with the minimum sequence number? My first thought was to group by and then use having min(seq no) (or something) but SQL developer doesn't let me do this at the end of the query:

select distinct a.id, .........., a.seq_no, .....
from .... a
inner join ... b
on a.id = b.id
left join .... c
on a.id = c.id
where .... and ..... and .... and ....
order by a.id;

when i remove the order by and try to do a group by, SQL developer complains

Any Ideas?
Thank you,
Ellie
P.S. Database is Banner (is for higher education, runs on top of Oracle 10g)
P.P.S. There's no write privileges, i.e. can't create a table and run subsequent queries on it must be done in one sitting. Am I asking for something impossible?

I don't know Oracle but perhaps something like:

SELECT column FROM table GROUP BY column HAVING ( COUNT(column) = 1 )

Hi. The following should work (if you are picking off the one record with minimum sequence value); it does assume that sub-queries are working ok.

select * from maintable
where seq_id in
(select min(seq_id)
from degreetable
group by studentid)

This code has the advantage that it will select those students (or perhaps I should say graduates) that have just the one entry.

Edit: I misread your post. You're after those students with just one degree? The above code will select all students with at least 1 degree (and choose the record with the lowest sequence). Change the code slightly and we have a solution:

select * from maintable
where studentid in
(select studentid
from degreetable
group by studentid having count(*) = 1)