Hello Friends I am struck up with typical problem.
I got this problem while querying data from Oracle Tables.
I can't explain the problem as it is with my project business jargons so
I am formulated the problem in following way.
Let us assume that table and data of it as given below:
TABLE : PLAYER
PLAYER NAME TEAM SCORE
Tendulkar IND 83
Tendulkar IND 42
Tendulkar IND 138
Tendulkar IND 67
Tendulkar BOMBAY 159
Dravid IND 32
Dravid IND 53
Dravid SZONE 72
Yuvaraj NZONE 91
Yuvaraj IND 27
Yuvaraj IND 42
Yuvaraj IND 12
Lara WI 83
Sewag IND 47
Sewag NZONE 17
I want the report based on the above table data as follows:
I want player name and his best 3 scores played for the team IND.
Report has to be look like as given below.
To get the following report output I need One-shot-SQL query? (I don't want any PL/SQL as solution)
PLAYER TEAM SCORE1 SCORE2 SCORE3
Tendulkar IND 138 83 67
Dravid IND 53 32
Yuvaraj IND 42 27 12
Sewag IND 47
I am giving u the table create script and data insert script for player table.
CREATE TABLE PLAYER (PNAME VARCHAR2(20),TEAM VARCHAR2(10), SCORE NUMBER(5));
INSERT INTO PLAYER VALUES ('Tendulkar','IND',138);
INSERT INTO PLAYER VALUES ('Tendulkar','IND',83);
INSERT INTO PLAYER VALUES ('Tendulkar','IND',42);
INSERT INTO PLAYER VALUES ('Tendulkar','IND',67);
INSERT INTO PLAYER VALUES ('Tendulkar','BOMBAY',159);
INSERT INTO PLAYER VALUES ('Dravid','IND',53);
INSERT INTO PLAYER VALUES ('Dravid','IND',32);
INSERT INTO PLAYER VALUES ('Dravid','SZONE',72);
INSERT INTO PLAYER VALUES ('Yuvaraj','NZONE',91);
INSERT INTO PLAYER VALUES ('Yuvaraj','IND',27);
INSERT INTO PLAYER VALUES ('Yuvaraj','IND',12);
INSERT INTO PLAYER VALUES ('Yuvaraj','IND',42);
INSERT INTO PLAYER VALUES ('Lara','WI',83);
INSERT INTO PLAYER VALUES ('Sewag','IND',47);
INSERT INTO PLAYER VALUES ('Sewag','NZONE',17);
