Re: SQL Puzzle - Finding Max Sequence

Try the folloing In SQL*Plus

Set serveroutput ON
DECLARE

	   CURSOR get_max  IS
	      select id, max(sequence_no) seq from grades
		group by id;
Grade1	varchar(1);
BEGIN
	   FOR arec IN get_max LOOP
	        SELECT grade into Grade1 from grades where
	        	id = arec.id and
	        	sequence_no = arec.seq;

   		  DBMS_OUTPUT.put_line('Student ' || arec.ID ||
   		  		' last grade: ' || grade1);

	   END LOOP;

END; In article <7u03ml$a4m$1_at_nnrp1.deja.com>,   papollo_bates_at_my-deja.com wrote:
> I have a table with three fields:
>
> 1) ID is a number field
> 2) Sequence_No is a number field
> 3) Grade is a VARCHAR2(1), holding A, B, C, D, E
>
> There are 100 individual IDs, each with more than one record. As a
> record is inserted for an ID, the next Sequence_No for that ID is
used.
> So, ID 100 has Sequence_No values of 1, 2, 3, 4, etc., as does ID 101
> and every other ID
>
> I want to select all IDs with a Grade of A or B, but I can only look
at
> their highest Sequence_No when checking their grade. I can"t figure
out
> the MAX(Sequence_No) part.
>
> Thanks!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 12 1999 - 15:59:40 CDT