3 reviews per postal code

pavan_test · #1 (**permalink**) 07-19-12, 16:18

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

anacedent · #2 (**permalink**) 07-19-12, 17:10

Where rownum < 4

pavan_test · #3 (**permalink**) 07-19-12, 17:16

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

anacedent · #4 (**permalink**) 07-19-12, 17:25

>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.

pavan_test · #5 (**permalink**) 07-19-12, 17:34

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

Littlefoot · #6 (**permalink**) 07-20-12, 01:13

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech