Home » SQL & PL/SQL » SQL & PL/SQL » Joining Query after selecting TOP N
Joining Query after selecting TOP N [message #256249] Fri, 03 August 2007 10:09 Go to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Thanks to searching on the FORUM, I've got some code to find bottom N records.

Not sure now how to join this to rows from another table. Here is my code:

SELECT NAME_ID, SCORE
FROM ( SELECT NAME_ID, SCORE from BCOFLOW.CS_RM order by SCORE asc)
WHERE rownum <= 10

I need to include in my results the CLIENTNAME which is on another table, which can be linked by NAME_ID.

Thanks

Re: Joining Query after selecting TOP N [message #256261 is a reply to message #256249] Fri, 03 August 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select ... 
from mytable a, (select ...) b 
where a.id=b.id 

Regards
Michel
Re: Joining Query after selecting TOP N [message #257023 is a reply to message #256261] Tue, 07 August 2007 07:18 Go to previous messageGo to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Thank you for your reply. I'm still struggling to get the results I want.

My code now looks like this:

SELECT ID, SCORE, MONTH, YEAR
FROM ( SELECT ID, SCORE, MONTH, YEAR
FROM TABLE1
ORDER BY ID, YEAR, MONTH asc)
WHERE rownum <= 3

This gives me the last 3 dates (MONTH & YEAR) but not at ID level
which is what I want. I just get the last 3 dates for all IDs

Help please!

Re: Joining Query after selecting TOP N [message #257028 is a reply to message #257023] Tue, 07 August 2007 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query does not look like mine. Is this another question?

Quote:
This gives me the last 3 dates (MONTH & YEAR) but not at ID level which is what I want. I just get the last 3 dates for all IDs

What do you want?
The last 3 dates for each ID?

Regards
Michel

Re: Joining Query after selecting TOP N [message #257032 is a reply to message #257028] Tue, 07 August 2007 07:36 Go to previous messageGo to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Thank you Michel for your reply.

Yes I need the last 3 dates for each id. It is only when I have done this that I then need to join the results to another table so that I can also display the ID_NAME in my report (re. my original message)

Really appreciate your help.
Re: Joining Query after selecting TOP N [message #257046 is a reply to message #257032] Tue, 07 August 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ename, sal, deptno
  2  from (select ename, sal, deptno,
  3               row_number() over (partition by deptno order by sal desc) rn
  4        from emp)
  5  where rn <= 2
  6   order by deptno, sal desc
  7  /
ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK            2450         10
MILLER           1300         10
ADAMS            1100         20
SMITH             800         20
ALLEN            1600         30
TURNER           1500         30

6 rows selected.

Then join this with your other table as per my first post.

Regards
Michel
Re: Joining Query after selecting TOP N [message #257085 is a reply to message #257046] Tue, 07 August 2007 10:07 Go to previous messageGo to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Thanks again for your time and patience Michel, I'm learning slowly!

This now almost works perfectly, but just can't get the correct syntax to join the final table. Here is what I am trying, which is to add the NAME from TABLE2 to the results from the first select:

SELECT ID, SCORE, month, year, date
FROM ( SELECT id,SCORE,MONTH,year,origdate,
row_number() over (partition by id order by year, MONTH asc) rn
from TABLE1
where origdate > '01/10/2006'
and status in ('Z', 'V', 'S'))
WHERE rn <= 3
and id in ('943', '391') a,

(select id, NAME from TABLE2) b
where a.id = b.id

Re: Joining Query after selecting TOP N [message #257108 is a reply to message #257085] Tue, 07 August 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts

You didn't add name in the final select list:
SQL> select b.dname, a.ename, a.sal
  2  from (select ename, sal, deptno
  3        from (select ename, sal, deptno,
  4                     row_number() over (partition by deptno order by sal desc) rn
  5              from emp)
  6        where rn <= 2) a,
  7       dept b
  8  where b.deptno = a.deptno
  9  order by b.dname, a.sal desc
 10  /
DNAME          ENAME             SAL
-------------- ---------- ----------
ACCOUNTING     KING             5000
ACCOUNTING     CLARK            2450
RESEARCH       FORD             3000
RESEARCH       JONES            2975
SALES          BLAKE            2850
SALES          ALLEN            1600

6 rows selected.

Regards
Michel
Re: Joining Query after selecting TOP N [message #257456 is a reply to message #257046] Wed, 08 August 2007 07:12 Go to previous message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Michel,

Many thanks for your help with this, I have learned a lot from the info you hvae provided.

I have a few more things to do with the code now, but I am very pleased with the results so far.
Previous Topic: connect by query
Next Topic: Accept Command in SQL*PLUS
Goto Forum:
  


Current Time: Thu Dec 08 20:19:54 CST 2016

Total time taken to generate the page: 0.05383 seconds