| Joining Query after selecting TOP N [message #256249] |
Fri, 03 August 2007 10:09  |
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 #257023 is a reply to message #256261] |
Tue, 07 August 2007 07:18  |
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 #257032 is a reply to message #257028] |
Tue, 07 August 2007 07:36 |
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 #257085 is a reply to message #257046] |
Tue, 07 August 2007 10:07 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
