selecting n number of rows from 2 tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: selecting n number of rows from 2 tables

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82

    Question

    I need to write a query that will return the last 15 rows from a 2 table join once certain criteria is met. The query is as follows:

    select col1,col2,col3
    from table1 a, table2 b
    where b.col1=a.col1
    and trunc(col3) <= (date)
    and col2 in ('text values')

    is there a simple way to get the last 15 rows of this query?

    I am using SQL*PLUS 8.1.6 on a 7.2.3 DB, users may be using SQL*PLUS 3.3

    thanks!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The term "the last 15 rows" is irrelevent without a sort order. Once you figure out your order, you can sort in decending order and pick the first 15 rows.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    so I put in a sort by the date field desc, then what? rownum < 16 where clause before the order by?

  4. #4
    Join Date
    Oct 2000
    Posts
    123
    Just for a hint, consider the following:

    SELECT
    ---OUTER.C1
    FROM
    ---(
    ------SELECT
    ---------INNER.C1,
    ---------ROWNUM
    ------------AS QUERY_ROWNUM
    ------FROM
    ---------(
    ---------SELECT
    ------------C1
    ---------FROM
    ------------TABLE1
    ---------ORDER BY
    ------------C1
    ---------) INNER
    ------WHERE
    ---------ROWNUM (LESS THAN) 8
    ---) OUTER
    WHERE
    ---OUTER.QUERY_ROWNUM >= 4

    ---- quoted from former topic

    Thanks

Posting Permissions

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


Click Here to Expand Forum to Full Width