Home » SQL & PL/SQL » SQL & PL/SQL » Problem with sub query and MAX
Problem with sub query and MAX [message #258128] Fri, 10 August 2007 06:18 Go to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
I've already had some help from this Forum to produce some SQL.

I am having trouble now trying to select the MAX value (starting from the code in red below).

What I want to do is selct the MAX date for each R_id (which I have done, but then using the R_id from the MAX, select more columns from another table...

I'm not sure how to code this....please help!

/**** first select the top 3 months and years ****/
select *
from (SELECT *
FROM ( *, row_number() over (partition by id order by year,MONTH asc) rn
from table1)
WHERE rn <= 3) a,
table2 b,
table3 c,

/**** select the latest reviews ****/
(SELECT MAX(orig_date) AS odate, R_id FROM table1
GROUP BY R_id) d
select????
where b.D_id = a.D_id
and c.R_id = a.R_id
AND a.R_id = d.R_id
Re: Problem with sub query and MAX [message #258145 is a reply to message #258128] Fri, 10 August 2007 07:03 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you elaborate on what you want by giving a create table, list of insert statements and expected output. Also please read how to format your posts.

thanks

Rajaram
Re: Problem with sub query and MAX [message #258150 is a reply to message #258128] Fri, 10 August 2007 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've already had some help from this Forum to produce some SQL.

But you still don't learn How to format your posts as requested in your previous post.

Regards
Michel
Re: Problem with sub query and MAX [message #258695 is a reply to message #258150] Mon, 13 August 2007 07:08 Go to previous messageGo to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Many apologies for both my poorly formatted and worded post. I hope this is better and I have tried to simplify my question:

select a.player_id, player_name from player_table a,
(select player_id, max(match_date)from match_table
group by player_id) b
where a.player_id = b.player_id


sample data:

player_table
p_id  p_name  
001   Smith    
002   Jones

match_table
p_id  m_date       m_id 
001   01/01/2007   111
001   01/02/2007   111 
002   01/01/2007   222 
002   01/04/2007   333

manager_table
m_id  m_name 
111   Brown
222   White
333   Black

results required:
p_id  p_name  m_date      m_name
001   Smith   01/02/2007  Brown   
002   Jones   01/04/2007  Black


I need to use the results from my code to join to the manager_table to find the manager_name. This is joined on m_id from match_table

I'm sure it must be simply a case of adding another select statement but can't work out how to do this correctly.

Thank you
Re: Problem with sub query and MAX [message #258700 is a reply to message #258695] Mon, 13 August 2007 07:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Untested code

select a.player_id, player_name, match_date, c.m_id, c.m_name from player_table a,
(select player_id, max(match_date) match_date, max(manager_id) manager_id from match_table
group by player_id) b, manager_table c
where a.player_id = b.player_id
and
b.manager_id = c.m_id 


P.S : Next time could you please post the insert script. It will be more helpful.

Thanks

Raj
Re: Problem with sub query and MAX [message #259060 is a reply to message #258700] Tue, 14 August 2007 05:39 Go to previous messageGo to next message
Reporter
Messages: 10
Registered: August 2007
Junior Member
Thank you very much for your help.

One thing I don't understand is the following line:

(select player_id, max(match_date) match_date, max(manager_id) manager_id from match_table


I need the manager_id that belongs to the latest match date. Would this code not give me the latest/highest manager_id?

Also, what does the statement mean
max(match_date) match_date,

where there is no comma in between?

As you will see my knowledge of SQL is very limited and I am actually trying to add this code directly into a Business Objects query....so I don't have an insert script to post.

Thanks again.
Re: Problem with sub query and MAX [message #259090 is a reply to message #259060] Tue, 14 August 2007 06:41 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
(select player_id, max(match_date) match_date, max(manager_id) manager_id from match_table

Why don't you try it and see it for yourself. Try tweaking the query, you will definitely understand it. If it is not what you expect come back to us with a sample data , expected output and what you have tried so far.
Quote:
max(match_date) match_date,

Read the oracle reference manual. You will know what it is.
For the insert script read the first two posts in this section of the forum, you will understand why it is required. Thanks for formatting your post.
Good Luck
Cheers
Raj
Previous Topic: QUERY regarding procedure
Next Topic: How to write a single query that delimits the string by count of 3?
Goto Forum:
  


Current Time: Mon Dec 05 04:42:21 CST 2016

Total time taken to generate the page: 0.14264 seconds