| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Puzzle - Finding Max Sequence
On Tue, 12 Oct 1999 19:55:10 GMT, 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.
>
Something like this might work.
clbeck_at_8i> desc foo
Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER SEQNO NUMBER GRADE VARCHAR2(1)
clbeck_at_8i> select * from foo;
ID SEQNO G
---------- ---------- -
101 1 A
101 2 B
101 3 A
102 1 C
103 1 D
103 2 B
103 3 F
7 rows selected.
clbeck_at_8i> select id, seqno, grade
2 from foo f1
3 where seqno >= ( select max(seqno)
4 from foo f2
5 where f1.id = f2.id
6 group by id );
ID SEQNO G
---------- ---------- -
101 3 A
102 1 C
103 3 F
>Thanks!
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |