Re: SQL Puzzle - Finding Max Sequence

From: Yosi Greenfield <ygreenfield_at_compuserve.com>
Date: Thu, 14 Oct 1999 14:43:32 -0400
Message-ID: <38062454.790C7ACD@compuserve.com>

Given:

ID      Seqnence_No     Grade
100               1         A
101               1         C
102               1         A
102               2         B
102               3         C
103               1         C
103               2         B
103               3         A

then:

select id, grade
from my_table
where (id, seq) in
  (select id, max(seq)
   from t2
   group by id)
and grade in ('A', 'B')
/

produces:

ID GRADE
--------- -----

      100 A
      103 A

HTH, Yosi

fumi wrote:

> <papollo_bates_at_my-deja.com> wrote in message
> news:7u03ml$a4m$1_at_nnrp1.deja.com...
> > 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.

> Your description is ambigious.
> For example, if the records are

>


> ID      Seqnence_No     Grade


> 100               1         A


> 101               1         C


> 102               1         A


> 102               2         B


> 102               3         C


> 103               1         C


> 103               2         B


> 103               3         A


>

> then, what do you want to list?
> Is ID 101 included?
> What is your "max sequence_no"?

> Describe your question more clearly and try to give a clear example,
> or nobody can give you a CORRECT answer.
Received on Thu Oct 14 1999 - 13:43:32 CDT