Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    34

    Unanswered: last row in a resultset or query

    Hi All

    Say If I have a complex query which returns a resultset say 15 rows. Now I want to limit the output showing only the last row.

    How can we do this

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Now I want to limit the output showing only the last row.
    Please realize that rows have NO, ZERO, NADA order in an RDBMS.
    If the row has 10 columns you could have at least ten DIFFERENT "last rows"; depending upon which column you are order the results!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    An intelligent application of MAX could get you what you want.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Posts
    9
    here a little example, you need something like GROUP BY in your statement:

    create table asdf (nummer number, text varchar2(30));

    insert into asdf values (1,'asdf');
    insert into asdf values (2,'qwer');
    insert into asdf values (3,'dsfg');
    insert into asdf values (4,'sdfgwert');
    insert into asdf values (5,'qwerfasdf');
    insert into asdf values (10,'asdf');
    insert into asdf values (20,'qwer');
    insert into asdf values (30,'dsfg');
    insert into asdf values(40,'sdfgwert');
    insert into asdf values (50,'qwerfasdf');
    insert into asdf values (100,'asdf');
    insert into asdf values (200,'qwer');
    insert into asdf values (300,'dsfg');
    insert into asdf values(400,'sdfgwert');
    insert into asdf values (500,'qwerfasdf');

    commit;

    select text,nummer
    from
    (
    select text,nummer,
    rank () over (partition by text order by nummer desc) "RANK"
    from asdf
    )
    where rank = 2;

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    in the above example, let's say you have this set and
    then you want the last row which would be "500 qwerfasdf".
    PHP Code:
    select from asdf;

        
    NUMMER TEXT
    ---------- ------------------------------
             
    1 asdf
             2 qwer
             3 dsfg
             4 sdfgwert
             5 qwerfasdf
            10 asdf
            20 qwer
            30 dsfg
            40 sdfgwert
            50 qwerfasdf
           100 asdf
           200 qwer
           300 dsfg
           400 sdfgwert
           500 qwerfasdf

    15 rows selected
    .

    Elapsed00:00:00.00
    select textnummer from
      2  
    (select text,nummer,
      
    3  count(*) over (order by rownumthe_order,
      
    4  count(*) over () the_last
      5  from asdf
    )
      
    6  where the_order the_last;

    TEXT                               NUMMER
    ------------------------------ ----------
    qwerfasdf                             500 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You could also do it by using a in-line view with an order by. For example

    select object_name
    from (select object_name
    from dba_objects
    order by object_name desc)
    where rownum = 1
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    what if there is no particular order (per-se) and the guy just wants
    the last row of the query?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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