Home » SQL & PL/SQL » SQL & PL/SQL » query to return entries with maximum column value
query to return entries with maximum column value [message #327484] Mon, 16 June 2008 09:39 Go to next message
tjahfe
Messages: 2
Registered: June 2008
Junior Member
Hi,
I currently have the following tables...
and executing SQLStatement1 will return Result1...
I wonder if there is a more efficient query that is equivalent to SQLStatement2 which will return Result2.

./fa/4456/0/


Please help,
Thank you in advance.
  • Attachment: ERD.PNG
    (Size: 40.09KB, Downloaded 162 times)
Re: query to return entries with maximum column value [message #327494 is a reply to message #327484] Mon, 16 June 2008 10:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If you don't mind could you please explain what you are trying to achieve in plain english words ?

Regards

Raj
Re: query to return entries with maximum column value [message #327545 is a reply to message #327494] Mon, 16 June 2008 19:33 Go to previous messageGo to next message
tjahfe
Messages: 2
Registered: June 2008
Junior Member
I'm trying to get a result list where
for each (proding_prod_id, proding_ing_id) combination,
if there exist an entry where proding_site_id is not null
then return the entry for that particular (proding_prod_id, proding_ing_id) 'group'

For example:
PRODING_PROD_ID PRODING_ING_ID SITE_ID
1 1 NULL
1 1 1
1 2 NULL
1 2 1
1 3 NULL

when (proding_prod_id, proding_ing_id) = (1,1)
there exist 2 entries:
1st entry has site_id = null
2nd entry has site_id = 1
-> include the 2nd entry in the result list

similarly,
when (proding_prod_id, proding_ing_id) = (1,2)
there exist 2 entries:
1st entry has site_id = null
2nd entry has site_id = 1
-> include the 2nd entry in the result list

on the other hand,
when (proding_prod_id, proding_ing_id) = (1,3)
there exists only 1 entry
-> include the only entry in the result list

and therefore, the end result will be:
PRODING_PROD_ID PRODING_ING_ID SITE_ID
1 1 1
1 2 1
1 3 NULL

I'm sorry if my explanation is not clear,
I'm not sure how to actually put it in words.

thanks
Re: query to return entries with maximum column value [message #327546 is a reply to message #327484] Mon, 16 June 2008 19:42 Go to previous message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
A table inside any RDBMS is like a basket filled with colored balls.

When multiple red balls exist, which red ball is the 2nd one?

Records in a table have NO inherent order.
Previous Topic: Help Needed in Bulk collect
Next Topic: SQL Query re-write assistance requested
Goto Forum:
  


Current Time: Fri Dec 02 12:43:51 CST 2016

Total time taken to generate the page: 0.14197 seconds