SQL Questions

Date: 06-Nov-2012

SQL: Inner / Sub query group wise problem

This blog contains questions which are different in problem solving approach and related to IT world. Origin is from day to day discussion / interview / other findings. 

Question:

Consider a schema

ID | Category | Keyword | Bid Price

Write a sql to fetch out top 5 keywords based on the bid price per category.

No RDBMS functions, only SQL.

Details/assumptions:

Table definition considered on oracle 10.x

create table test (
ID number,
Category varchar (20),
Keyword varchar (20),
BidPrice number
);

Data:

insert into test values (1, 'Category-A', 'Keyword-A1', 110);
insert into test values (2, 'Category-A', 'Keyword-A2', 121);
insert into test values (3, 'Category-A', 'Keyword-A3', 130);
insert into test values (4, 'Category-A', 'Keyword-A4', 125);
insert into test values (5, 'Category-A', 'Keyword-A5', 115);
insert into test values (6, 'Category-A', 'Keyword-A6', 133);
insert into test values (7, 'Category-B', 'Keyword-B1', 105);
insert into test values (8, 'Category-B', 'Keyword-B2', 111);
insert into test values (9, 'Category-B', 'Keyword-B3', 108);
insert into test values (10, 'Category-B', 'Keyword-B4', 128);
insert into test values (11, 'Category-B', 'Keyword-B5', 144);
insert into test values (12, 'Category-B', 'Keyword-B6', 101);
insert into test values (13, 'Category-C', 'Keyword-C1', 150);
insert into test values (14, 'Category-C', 'Keyword-C2', 137);
insert into test values (15, 'Category-C', 'Keyword-C3', 126);
insert into test values (16, 'Category-C', 'Keyword-C4', 121);
insert into test values (17, 'Category-C', 'Keyword-C5', 112);
insert into test values (18, 'Category-C', 'Keyword-C6', 118);

Output expected:

KEYWORD       CATEGORY     BIDPRICE
------------- ------------- ----------
Keyword-A3      Category-A   130
Keyword-A4      Category-A   125
Keyword-A2      Category-A   121
Keyword-A5      Category-A   115
Keyword-A1      Category-A   110
Keyword-B5      Category-B   144
Keyword-B4      Category-B   128
Keyword-B2      Category-B   111
Keyword-B3      Category-B   108
Keyword-B1      Category-B   105
Keyword-C1      Category-C   150
Keyword-C2      Category-C   137
Keyword-C3      Category-C   126
Keyword-C4      Category-C   121
Keyword-C5      Category-C   112

Answer:

Till this point all the provided ingredients are enough to cook our sql for a given scenario.  Still if you do not want to try your own and want to see result have a look:

Oracle specific solution:

SELECT KEYWORD ,CATEGORY, BIDPRICE
FROM
(
  SELECT KEYWORD ,CATEGORY, BIDPRICE,
          ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY BIDPRICE DESC) rn
  FROM test
) a
WHERE rn <= 5
ORDER BY CATEGORY, BIDPRICE DESC

Final Solution:
Below is the only SQL solution which is accepted as a final solution is:

SELECT 

    A.KEYWORD, A.CATEGORY, A.BIDPRICE 

FROM TEST A,  (

        SELECT 

            B.ID, B.CATEGORY 

        FROM TEST B 

        WHERE 

        (

            SELECT 

                DISTINCT(C.CATEGORY) 

            FROM TEST C 

            WHERE 

                B.CATEGORY=C.CATEGORY

        ) = B.CATEGORY 

        ORDER BY B.BIDPRICE DESC

    ) D WHERE A.ID IN 

        (SELECT ID FROM 

            (SELECT E.ID, E.CATEGORY FROM TEST E ORDER BY E.BIDPRICEDESC) F 

        WHERE A.CATEGORY=F.CATEGORY AND ROWNUM<=5

    ) 

GROUP BY A.CATEGORY, A.BIDPRICE, A.KEYWORD ORDER BY A.CATEGORY, A.BIDPRICE DESC;

Please let me know if you have any question.

Thanks
Shailendra