Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    China
    Posts
    38

    Unanswered: How to get top record?

    For example,a table named TableName has 3 records as following:

    Id ParentId Dscr
    ---------------------------------
    1 2 son
    2 3 father
    3 -1 grandpa

    And there's no order on any column.
    I have the son's ID,and I can get this result by this query:
    SELECT * FROM TableName START WITH Id = 1 CONNECT BY PRIOR ParentId = Id
    But how can I get the top record (3,-1,grandpa) only by one SQL statement?

    Thanks!
    Last edited by 884813; 06-08-04 at 04:26.
    Fan Yi

  2. #2
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi Fan Yi!

    Try this:
    PHP Code:
    SELECT IdParentIdDscr
    FROM 
    (SELECT IdParentIdDscr 
          FROM TableName 
          ORDER BY Id DESC

    WHERE ROWNUM 1
    Regards,
    Julia

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    add a where clause - where parent_id = -1

  4. #4
    Join Date
    Jan 2003
    Location
    China
    Posts
    38
    Thanks at first!
    But I said that there's no order on any column,the son's,father's and the garndpa's Id here are 1,2,3, but they can be 3,1,2 also.So I can't use 'ORDER BY' to realize this.
    Thanks!
    Last edited by 884813; 06-08-04 at 23:52.
    Fan Yi

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Do you have something like Timestamp, e.g. CREATED_DATE ?
    How do you want to sort your query? On what criterias?
    Last edited by julla27; 06-09-04 at 05:00.
    Regards,
    Julia

  6. #6
    Join Date
    Jan 2003
    Location
    China
    Posts
    38
    With a friend's help,I realize it by this statement:

    SELECT Id,ParentId,Dscr
    FROM (SELECT Id,ParentId,Dscr
    FROM(SELECT Id,ParentId,Dscr,rownum AS RN
    FROM TableName START WITH Id = 1 CONNECT BY PRIOR ParentId = Id)
    ORDER BY RN DSEC)
    WHERE rownum = 1

    His suggestion is that:
    generally speaking, you return the results of a query and if you want the top you use rownum = 1, if you want the bottom you can then order the set in reverse order and apply rownum = 1

    Thanks alot!
    Last edited by 884813; 06-09-04 at 22:41.
    Fan Yi

Posting Permissions

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