Home » SQL & PL/SQL » SQL & PL/SQL » Query (Oracle, 10g, Windows 2003 server )
Query [message #441225] Fri, 29 January 2010 09:53 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
I have a table named test2 having three columns
rating varchar2(3),
seniority varchar2(100),
effdt Date.

SQL> Select * From test4;

SENIORITY     RATING     EFFDT

DESIRE HP     A          01-APR-10
DESIRE HP     A          11-APR-10
DESIRE HP     A          12-APR-10
DESIRE HP     A          17-APR-10
DESIRE HP     A          18-APR-10
ENIGMA        A          01-APR-10
ENIGMA        A          02-APR-10
ENIGMA        A          04-APR-10
ENIGMA        A          16-DEC-10
ENIGMA        A          16-NOV-10
ENIGMA        A          16-JAN-10
ENIGMA        A          16-FEB-10
ENIGMA        A          17-FEB-10
ENIGMA HP     A          19-FEB-10
HEWLET P      A          20-APR-10
HEWLET P      A          21-APR-10
HEWLET P      A          23-APR-10
HP            A          24-APR-10
SAMURAI       A          26-APR-10
SAMURAI       A          30-APR-10 
SAMURAI       A          01-JUL-10
SAMURAI       A          03-JUL-10
SAMURAI       A          04-AUG-10
SAMURAI       A          06-AUG-10
SAMURAI       A          08-AUG-10
SAMURAI       A          11-AUG-10


I want two queries giving output as follows:
1) First query should give only one record for each seniority for the latest effective date as shown below


SENIORITY     RATING     EFFDT

DESIRE HP     A          18-APR-10
ENIGMA        A          16-DEC-10
ENIGMA HP     A          19-FEB-10
HEWLET P      A          23-APR-10
HP            A          24-APR-10
SAMURAI       A          11-AUG-10


2)Second query should give u 3 or less records for each seniority
ordered by effdt in descending order, but it should not include the record got in first query for that particular seniority as shown below


SENIORITY     RATING     EFFDT

DESIRE HP     A          17-APR-10
DESIRE HP     A          12-APR-10
DESIRE HP     A          11-APR-10
ENIGMA        A          16-NOV-10
ENIGMA        A          04-APR-10
ENIGMA        A          02-APR-10
HEWLET P      A          21-APR-10
HEWLET P      A          20-APR-10
SAMURAI       A          08-AUG-10
SAMURAI       A          06-AUG-10
SAMURAI       A          04-AUG-10



Regards,
Ritesh


Re: Query [message #441228 is a reply to message #441225] Fri, 29 January 2010 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

Regards
Michel
Re: Query [message #441232 is a reply to message #441225] Fri, 29 January 2010 10:35 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
Here is the create table statement


SQL> Create Table test2(seniority varchar2(100),rating Varchar2(3), effdt Date);

Table created.


Following are the insert statements


SQL> Insert Into test2 values('DESIRE HP','A',To_Date('01-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('DESIRE HP','A',To_Date('11-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('DESIRE HP','A',To_Date('12-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('DESIRE HP','A',To_Date('17-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('DESIRE HP','A',To_Date('18-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('01-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('02-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('04-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('16-DEC-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('16-NOV-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('16-JAN-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('16-FEB-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA','A',To_Date('17-FEB-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('ENIGMA HP','A',To_Date('19-FEB-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('HEWLET P','A',To_Date('20-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('HEWLET P','A',To_Date('21-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('HEWLET P','A',To_Date('23-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('HP','A',To_Date('24-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('26-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('30-APR-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('01-JUL-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('03-JUL-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('04-AUG-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('06-AUG-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('08-AUG-10','DD-MON-YY'));

1 row created.

SQL> Insert Into test2 values('SAMURAI','A',To_Date('11-AUG-10','DD-MON-YY'));

1 row created.


Regards,
Ritesh
icon12.gif  Re: Query [message #441245 is a reply to message #441232] Fri, 29 January 2010 13:27 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Now that michel gave you the necesary "hints" on how to solve the queries, maybe you could try and do the homework yourself?
./fa/1600/0/

Re: Query [message #441247 is a reply to message #441232] Fri, 29 January 2010 13:33 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select SENIORITY, RATING, EFFDT
  2  from ( select t.*, rank() over(partition by seniority order by effdt desc) rk
  3         from test2 t
  4       )
  5  where rk = 1
  6  order by SENIORITY
  7  /
SENIORITY            RAT EFFDT
-------------------- --- -----------
DESIRE HP            A   18-APR-2010
ENIGMA               A   16-DEC-2010
ENIGMA HP            A   19-FEB-2010
HEWLET P             A   23-APR-2010
HP                   A   24-APR-2010
SAMURAI              A   11-AUG-2010

6 rows selected.

SQL> select SENIORITY, RATING, EFFDT
  2  from ( select t.*, rank() over(partition by seniority order by effdt desc) rk
  3         from test2 t
  4       )
  5  where rk between 2 and 4
  6  order by SENIORITY
  7  /
SENIORITY            RAT EFFDT
-------------------- --- -----------
DESIRE HP            A   17-APR-2010
DESIRE HP            A   12-APR-2010
DESIRE HP            A   11-APR-2010
ENIGMA               A   16-NOV-2010
ENIGMA               A   04-APR-2010
ENIGMA               A   02-APR-2010
HEWLET P             A   21-APR-2010
HEWLET P             A   20-APR-2010
SAMURAI              A   08-AUG-2010
SAMURAI              A   06-AUG-2010
SAMURAI              A   04-AUG-2010

11 rows selected.

Regards
Michel
Previous Topic: Split Sting
Next Topic: Converting columns into rows
Goto Forum:
  


Current Time: Mon Sep 26 23:09:04 CDT 2016

Total time taken to generate the page: 0.08350 seconds