Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: row_number() question

    Hi,

    I have a typical parent/child scenario with each stored in table1 and table2 respectively. I am trying to limit the resultset to the first 5 rows of the detail table (table2).

    Consider the following query.

    select t1.field1, t2.field1 from table1 t1, (select *, row_number() over (partition by content_id) rn from table2) t2 where t1.field1 = t2.field1 and rn <=5

    This works pretty good but what about the case where there are no matching children? In this case I still want the parent record to return similar to a left outer join.

    Consider the revised query using a left outer join.

    select t1.field1, t2.field1 from table1 t1 left outer join (select *, row_number() over (partition by content_id) rn from table2) t2 on t1.field1 = t2.field1 where rn <=5

    Unfortunately this still excludes the parent records that don't have matching child records.

    Am I doing something wrong?

    Is there a better approach?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am trying to limit the resultset to the first 5 rows of the detail table
    rows in a table are like balls in a basket.
    Which are the first 5 balls in the basket?

    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.

    First make it work, then make it fancy.
    First get the desired result set, then work to limit the number of rows returned.
    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
    Dec 2007
    Location
    London, UK
    Posts
    741
    Try this (changed the final condition to the ON clause instead of WHERE):

    SELECT t1.field1, t2.field1
    FROM table1 t1
    LEFT OUTER JOIN
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY content_id) rn
    FROM table2) t2
    ON t1.field1 = t2.field1
    AND rn <=5 ;

Posting Permissions

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