Home » SQL & PL/SQL » SQL & PL/SQL » Is it Possible?
Is it Possible? [message #316422] Fri, 25 April 2008 01:57 Go to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Hi everyone,
I have 3 tables listed below. I want a query that display F_Nbr, F_Name, and current F_rank_Desc (highest rank of that faculty id). I've tried several ways but haven't had the expected result. Is it possible to do that or I just waste my time?
Table Faculty
     F_NBR F_NAME
---------- ---------------
       119 Barb Martin
       421 Al Jones
       462 Tom Johnson
       209 Jane Newton
       485 Pam Sparks
       507 Rob Canton
       564 Tim Ferris
       683 Frank Nilsen
       710 Ramon Garcia

Table Fac_Rank
     F_NBR  F_RANK_ID DATE_ATT
---------- ---------- ---------
       119          1 15-SEP-93
       209          1 01-SEP-98
       209          2 13-SEP-96
       421          1 15-AUG-00
       462          1 12-APR-83
       462          2 14-SEP-93
       485          1
       485          2 12-SEP-87
       485          3 20-SEP-96
       507          1 01-SEP-94
       564          1 21-MAY-89
       564          2 01-JAN-00
       683          1 11-JUN-79
       683          2 02-AUG-87
       683          3 12-JAN-96
       710          1
       710          2 15-DEC-99

Table Rank
 F_RANK_ID F_RANK_DES
---------- ----------
         1 Asst Prof
         2 Asso Prof
         3 Prof 

The result should look like this with F_name & F_Rank_Desc instead of F_Rank_ID,
     F_NBR MAX(F_RANK_ID)
---------- --------------
       119              1
       209              2
       421              1
       462              2
       485              3
       507              1
       564              2
       683              3
       710              2

9 rows selected.


Regards
Re: Is it Possible? [message #316430 is a reply to message #316422] Fri, 25 April 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you know you have to provide:
1/ A test case (create table and insert statements)
2/ What you already tried and why it does not fit your requirements
3/ Your Oracle version

Regards
Michel
Re: Is it Possible? [message #316436 is a reply to message #316422] Fri, 25 April 2008 02:25 Go to previous messageGo to next message
deepakkrjain
Messages: 4
Registered: April 2007
Location: Delhi
Junior Member

Dear Friend,


Used this Query. It's solved your Problem.

select d.f_nbr,d.f_name,max(e.f_rank_id) from faculty d, fac_rank e
where e.f_nbr=d.f_nbr
group by d.f_nbr,d.f_name;



Regards,

Re: Is it Possible? [message #316440 is a reply to message #316430] Fri, 25 April 2008 02:32 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member

Hi michel,
This is what I have tried:
SQL> select F_Nbr, F_Name, F_Rank_Desc
  2  from faculty JOIN Fac_Rank Using(F_Nbr) JOIN Rank Using(F_Rank_ID)
  3  order By Max(F_Rank_ID);

     F_NBR F_NAME          F_RANK_DES
---------- --------------- ----------
       119 Barb Martin     Asst Prof
       421 Al Jones        Asst Prof
       462 Tom Johnson     Asst Prof
       209 Jane Newton     Asst Prof
       485 Pam Sparks      Asst Prof
       507 Rob Canton      Asst Prof
       564 Tim Ferris      Asst Prof
       683 Frank Nilsen    Asst Prof
       710 Ramon Garcia    Asst Prof
       462 Tom Johnson     Asso Prof
       209 Jane Newton     Asso Prof

     F_NBR F_NAME          F_RANK_DES
---------- --------------- ----------
       485 Pam Sparks      Asso Prof
       564 Tim Ferris      Asso Prof
       683 Frank Nilsen    Asso Prof
       710 Ramon Garcia    Asso Prof
       485 Pam Sparks      Prof
       683 Frank Nilsen    Prof

17 rows selected.

SQL> select distinct F_Nbr, F_Name, F_Rank_Desc
  2  from faculty JOIN Fac_Rank Using(F_Nbr) JOIN Rank Using(F_Rank_ID)
  3  order By Max(F_Rank_ID);

     F_NBR F_NAME          F_RANK_DES
---------- --------------- ----------
       119 Barb Martin     Asst Prof
       421 Al Jones        Asst Prof
       462 Tom Johnson     Asst Prof
       209 Jane Newton     Asst Prof
       485 Pam Sparks      Asst Prof
       507 Rob Canton      Asst Prof
       564 Tim Ferris      Asst Prof
       683 Frank Nilsen    Asst Prof
       710 Ramon Garcia    Asst Prof
       462 Tom Johnson     Asso Prof
       209 Jane Newton     Asso Prof

     F_NBR F_NAME          F_RANK_DES
---------- --------------- ----------
       485 Pam Sparks      Asso Prof
       564 Tim Ferris      Asso Prof
       683 Frank Nilsen    Asso Prof
       710 Ramon Garcia    Asso Prof
       485 Pam Sparks      Prof
       683 Frank Nilsen    Prof

17 rows selected.

SQL> select F_Nbr, F_Name, F_Rank_Desc
  2  from faculty JOIN Fac_Rank Using(F_Nbr) JOIN Rank Using(F_Rank_ID)
  3  Where F_Rank_ID = (Select Max(F_Rank_ID) From Rank);

     F_NBR F_NAME          F_RANK_DES
---------- --------------- ----------
       485 Pam Sparks      Prof
       683 Frank Nilsen    Prof

It should be 9 rows only because there are only 9 faculty members. By the way, I'm using oracle 10g.

Regards
Re: Is it Possible? [message #316445 is a reply to message #316436] Fri, 25 April 2008 02:52 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Hi Deepakkrjain,
Thanks for your input. But that's not What I expected. A query should retrieve:
 F_Nbr   F_Name        F_Rank_Desc
 119     Barb Martin   Asst Prof
 209     Jane Newton   Asso Prof
 421     Al Jones      Asst Prof
 462     Tom Johnson   Asso Prof
 485     Pam Sparks    Prof

and so on.

Regards

[Updated on: Fri, 25 April 2008 02:53]

Report message to a moderator

Re: Is it Possible? [message #316448 is a reply to message #316440] Fri, 25 April 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Is it Possible? [message #316916 is a reply to message #316422] Mon, 28 April 2008 19:51 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
A few points:

1) Yes, this is possible, because

2) A query from a subquery with something like
select tf.f_nbr fac_number,
             tf.f_name fac_name,
             tr.f_rank_des rank_desc,
             fr.date_att date_att,
             max(fr.date_att) over(partition by tf.f_nbr) max_att

in the select list, and the necessary table and predicate list will produce the desired answer. However

3) The sample "correct" output is not consistent with the problem specifications. Specifically, Jane Newton was demoted on 01-SEP-98
from the position she held on 13-SEP-96. So, she should appear as an Asst Prof, but the sample output has her as an Asso Prof. Which leads me to wonder if this is a homework problem?

4) Since I have only been participating a few days, I hesitate to go any further on a homework problem, as I don't have a good sense for how this forum handles such things. I would love to show off my wonderfullness, but I don't want to break protocol.

5) Michel: I tackled this because it looked like a good exercise in analytic functions (and it was). But, I don't see how the RANK and DENSE RANK functions would have helped. Any hint as to what I am missing?
Re: Is it Possible? [message #317164 is a reply to message #316916] Tue, 29 April 2008 14:29 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
This seems to work:

SELECT f_nbr, f_name, f_rank_desc
  FROM (SELECT f_nbr, f_name, f_rank_desc, f_rank_id, 
               MAX(f_rank_id) over(partition by f_nbr) max_rank_id
          FROM faculty JOIN
               fac_rank USING (f_nbr) JOIN
               rank USING (f_rank_id))
 WHERE f_rank_id = max_rank_id


F_NBR F_NAME                                             F_RANK_DESC         
----- -------------------------------------------------- --------------------
  119 Barb Martin                                        Asst Prof           
  209 Jane Newton                                        Asso Prof           
  421 Al Jones                                           Asst Prof           
  462 Tom Johnson                                        Asso Prof           
  485 Pam Sparks                                         Prof                
  507 Rob Canton                                         Asst Prof           
  564 Tim Ferris                                         Asso Prof           
  683 Frank Nilsen                                       Prof                
  710 Ramon Garcia                                       Asso Prof           
Re: Is it Possible? [message #317375 is a reply to message #316422] Wed, 30 April 2008 20:29 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Er... No.

At least, not if my point (3) has any merit. Jane Newton was made an Associate Prof in 1996. In 1998 she was made an Assistant Prof. Since the specifications are for the current position, she should be listed as an Assistant Prof rather than an Associate Prof.

The fix, of course, is to use the f_date_att instead of the f_rank_id.
Re: Is it Possible? [message #317698 is a reply to message #316422] Fri, 02 May 2008 11:25 Go to previous message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Quite right.

SELECT f_nbr, f_name, f_rank_desc
  FROM (SELECT f_nbr, f_name, f_rank_desc, f_date_att, 
               MAX(f_date_att) over(partition by f_nbr) max_date_att
          FROM faculty JOIN
               fac_rank USING (f_nbr) JOIN
               rank USING (f_rank_id))
 WHERE f_date_att = max_date_att
Previous Topic: Network location for external table's file
Next Topic: Rewrite my MSSQL procedure to Oracle syntax
Goto Forum:
  


Current Time: Mon Dec 05 14:48:17 CST 2016

Total time taken to generate the page: 0.05810 seconds