Results 1 to 5 of 5

Thread: Pull last four

  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: Pull last four

    I'm trying to pull the last 4 records from an Oracle database and the SQL code isn't quite working....All of the 'real-names' (i.e. proptype, ect) are because this is in Crystal; however, the error I'm getting says the From keyword is not found where expected....

    Any help would be greatly appreciated.

    Thanks
    Nick

    Code:
    Select p.sAddr1 name TOP 4
    , p.sCode propcode
    , attributes.SUBGROUP2 proptype
    , attributes.SUBGROUP1 advisor
    , attributes.SUBGROUP13 status
    , ASCAP15.dtDate dt
    , ASCAP15.A_AFTOTALRETURN af_totret
    , ASCAP15.A_AFINCOMERETURN af_incret
    , ASCAP15.A_AFAPPRECRETURN af_appret
    From  Property p , Attributes , ASCAP15 
    Where      p.hMy = attributes.hProp and  p.hMy = ASCAP15.hProp (+)  and attributes.SUBGROUP2<> ' ' and attributes.SUBGROUP13<> 'Proposed Purch'
    ORDER BY p.sAddr1 DESC, ASCAP15.dtDate ASC

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oracle does not support TOP

    change your query starting right there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    Hmmm...I didn't know that. Any suggestions as to what I should use instead?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There are ways to fetch "top N" records; here are two of them (examples are based on Scott's schema):
    Code:
    SELECT ename, sal
    FROM (SELECT ename, sal
          FROM EMP
          ORDER BY sal DESC
         )
    WHERE ROWNUM <= 4
    Code:
    SELECT ename, sal
    FROM (SELECT ename, sal, rank() over (ORDER BY sal DESC) rn
          FROM EMP
         )
    WHERE rn <= 4

  5. #5
    Join Date
    Feb 2008
    Posts
    3
    Thanks...I'll give those a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •