Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    92

    Unanswered: 3 reviews per postal code

    hello,

    I have 2 tables reviews and postal code.

    table reviews: has 2 column party ID and review
    table postal code: also has 2 column party id and zip code.

    I have to retrieve 3 reviews per postal code.

    can someone please tell me how do i write a sql query.

    Thanks
    pavan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Where rownum < 4
    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
    Oct 2005
    Posts
    92

    3 reviews per postal code

    I tried rownum<4 it retured first 3 records in the table.

    select postal_cd, review
    from postal_code, reviews
    where postal_code.pty_id = reviews.pty_id
    and rownum <4


    I am looking for 3 reviews for each postal code in the 2 tables.

    Thanks
    Pavan

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >select postal_cd, review
    above is interesting since POSTAL_CD did not exist in orginal post.

    >I tried rownum<4 it retured first 3 records in the table.
    OK, but this only means the SQL needs to be changed to actually meet the requirements.
    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.

  5. #5
    Join Date
    Oct 2005
    Posts
    92

    3 reviews per postal code

    Can somoone please tell me how can I retreive 3 rows per group.

    I have 2 tables reviews and postal code.

    table reviews: has 2 column party ID and review
    table postal code: also has 2 column party id and zip code. (or postal code)

    I have to retrieve 3 reviews per postal code.

    Thanks
    Pavan

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example based on Scott's schema.

    Employees, sorted by department numbers and their names:
    Code:
    SQL> select deptno, ename from emp
      2  order by deptno, ename;
    
        DEPTNO ENAME
    ---------- ----------
            10 CLARK
            10 KING
            10 MILLER
            20 ADAMS
            20 FORD
            20 JONES
            20 SCOTT
            20 SMITH
            30 ALLEN
            30 BLAKE
            30 JAMES
            30 MARTIN
            30 TURNER
            30 WARD
    
    14 rows selected.
    
    SQL>
    I want to select 2 employees per each department; here's one option:
    Code:
    SQL> select deptno, ename
      2  from
      3    (select deptno,
      4            ename,
      5            row_number() over (partition by deptno order by deptno, ename) rn
      6     from emp
      7    )
      8  where rn <= 2;
    
        DEPTNO ENAME
    ---------- ----------
            10 CLARK
            10 KING
            20 ADAMS
            20 FORD
            30 ALLEN
            30 BLAKE
    
    6 rows selected.
    
    SQL>
    Now, apply that to your data.

Posting Permissions

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