Home » SQL & PL/SQL » SQL & PL/SQL » Max Query Help!!!
Max Query Help!!! [message #233303] Wed, 25 April 2007 10:37 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have this following query I'm running:

select distinct
a.id,
b.lname,
b.fname,
d.value,
e.country
from
table1 a, table2 b, table3 c, table4 d, table5 e
where
b.p_id = c.p_id and
a.an_id = c.an_id and
b.p_id = d.p_id (+) and
a.country_id = e.country_id (+)

I could have more than one country for a person. So, what I'm getting back is the following:

ID LNAME FNAME VALUE COUNTRY
1 Joe Smith 55 France
2 Joe Smith 55 US
3 Mary Smith 33 US
4 Tim Tom 44 US
5 Tim Tom 44 Germany

This is what I'm trying to get. Basically the record based on the max ID for each person:

ID LNAME FNAME VALUE COUNTRY
2 Joe Smith 55 US
3 Mary Smith 33 US
5 Tim Tom 44 Germany

Thanks in advance for your help!!!

[Updated on: Wed, 25 April 2007 21:27]

Report message to a moderator

Re: Max Query Help!!! [message #233304 is a reply to message #233303] Wed, 25 April 2007 10:45 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Before venturing an opinion , I would like to see the structure and some sample data for these tables.

Srini
Re: Max Query Help!!! [message #233311 is a reply to message #233303] Wed, 25 April 2007 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with yourdata as (your query),
mydata as (
select y.*, row_number() over (partition by lname order by id desc) rn
from yourdata
)
select * from mydata where rn=1
/

Regards
Michel

Re: Max Query Help!!! [message #233315 is a reply to message #233304] Wed, 25 April 2007 11:14 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Here's the requested data:

Table1
------
ID COUNTRY_ID AN_ID
1 1 111
2 2 111
3 2 222
4 2 333
5 3 333

Table2
------
P_ID LNAME FNAME
1 Joe Smith
2 Mary Smith
3 Tim Tom
4 John Doe

Table3
------
AN_ID P_ID
111 1
222 2
333 3

Table4
------
VALUE_ID VALUE P_ID
1 55 1
2 33 2
3 44 3

Table5
-----
COUNTRY_ID COUNTRY
1 France
2 US
3 Germany
Re: Max Query Help!!! [message #233318 is a reply to message #233311] Wed, 25 April 2007 11:38 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Michel,
I don't get how to run your solution
Re: Max Query Help!!! [message #233321 is a reply to message #233318] Wed, 25 April 2007 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you get?
What did you try?

Regards
Michel

Re: Max Query Help!!! [message #233350 is a reply to message #233303] Wed, 25 April 2007 15:40 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
select
a.id
b.lname,
b.fname,
d.value,
max(e.country) country
from
table1 a, table2 b, table3 c, table4 d, table5 e
where
b.p_id = c.p_id and
a.an_id = c.an_id and
b.p_id = d.p_id (+) and
and a.country_id = e.country_id (+)
group by a.id,b.lname,b.fname,d.value;
Re: Max Query Help!!! [message #233356 is a reply to message #233318] Wed, 25 April 2007 16:26 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
How you fit your query within my query
Re: Max Query Help!!! [message #233357 is a reply to message #233350] Wed, 25 April 2007 16:27 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Bill,
this wouldn't work as country might be different in come cases could be Canada and then Brazil.
Re: Max Query Help!!! [message #233358 is a reply to message #233356] Wed, 25 April 2007 16:28 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
lowcash75 wrote on Wed, 25 April 2007 16:26
How you fit your query within my query

It is a replacement for your query doing what you wanted. run it against your database with the correct column and table names and see what happens.
Re: Max Query Help!!! [message #233359 is a reply to message #233357] Wed, 25 April 2007 16:29 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
lowcash75 wrote on Wed, 25 April 2007 16:27
Bill,
this wouldn't work as country might be different in come cases could be Canada and then Brazil.

It will return ONLY one of the countries. You wanted the max country name and that is what it will do.
Re: Max Query Help!!! [message #233375 is a reply to message #233359] Wed, 25 April 2007 21:32 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
That didn't work

This is the result I get back
1 Joe Smith 55 France
2 Joe Smith 55 US
3 Mary Smith 33 US
4 Tim Tom 44 US
5 Tim Tom 44 Germany

I need to get back results for max ID for a person.
Re: Max Query Help!!! [message #233376 is a reply to message #233311] Wed, 25 April 2007 21:36 Go to previous message
lowcash75
Messages: 67
Registered: February 2006
Member
Michel,
I finally got it work!!!!

Thanks for your help!!
Previous Topic: How do you count explicit nested_table and incorporate it in the logic
Next Topic: Constraint Error
Goto Forum:
  


Current Time: Sat Dec 03 07:43:54 CST 2016

Total time taken to generate the page: 0.04591 seconds