Re: Finding Last 4 occurrences

From: <rtproffitt_at_my-deja.com>
Date: Wed, 24 Nov 1999 17:32:14 GMT
Message-ID: <81h7eu$3gr$1@nnrp1.deja.com>

Here's another approach....
Granted, it's "brute force", and I don't know how Fast it will be. It will only work if 4 IS the number you wish: it is not a general solution. Also, if two time keys are identical, then you might get more than four records.....
Anyway, here it is for academic's sake:

RT>select * from bob order by timekey desc;

  TIMEKEY PN
--------- ----------

       10 B
        9 B
        8 B
        7 B
        6 B
        5 A
        5 B
        4 A
        3 A
        2 A
        1 A

Desired result will be A: 5,4,3,2 and B: 10,9,8,7

Select * from bob f
where timekey >

    (/* returns four less than max */
    Select max(timekey)
    from bob e
    where e.pn = f.pn
    And timekey <

      (/* returns three less than max */
      Select max(timekey)
      from bob d
      where d.pn = e.pn
      And timekey <
        ( /* returns two less than max */
        Select max(timekey)
        from bob c
        where c.pn = d.pn
        And timekey <
          ( /* returns one less than max */
          Select max(timekey)
          from bob b
          where b.pn = c.pn
          And timekey <
            (/* gets the max */
             Select Max(timekey)
             From Bob a
             where a.pn=b.pn)

    ) ) ) )
order by pn, timekey desc

RESULT
  TIMEKEY PN
--------- ----------

        5 A
        4 A
        3 A
        2 A
       10 B
        9 B
        8 B
        7 B

Good Luck
Robert Proffitt

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 24 1999 - 11:32:14 CST