Home » SQL & PL/SQL » SQL & PL/SQL » MIN fUNCTION WITH GROUP BY
MIN fUNCTION WITH GROUP BY [message #350866] Fri, 26 September 2008 14:46 Go to next message
sammtron
Messages: 4
Registered: September 2008
Junior Member
Hi I need some help with this
I have a table whose columns are
Col1 Col2 Col3
123 1 S
123 2 P
235 1 S
235 2 P
235 3 X
456 2 P
456 3 Z
What I need is a query which would help me get the min value of Col2 and its equivalent Col1 and Col3

Like
Col1 Col2 Col3
123 1 S
235 1 S
456 2 P
What do I do?

Thank you in advance...
Re: MIN fUNCTION WITH GROUP BY [message #350869 is a reply to message #350866] Fri, 26 September 2008 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: MIN fUNCTION WITH GROUP BY [message #350871 is a reply to message #350866] Fri, 26 September 2008 15:14 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I think you explained incorrectly. If you want the minumum value of COL2 and its equivalent COL1 and COL3, then you cannot have two rows with value of 1 for COL2.
Re: MIN fUNCTION WITH GROUP BY [message #350872 is a reply to message #350866] Fri, 26 September 2008 15:28 Go to previous messageGo to next message
sammtron
Messages: 4
Registered: September 2008
Junior Member
Sorry for my Formatting...
I need to find the min value of col2 for every id(like 123) in col1 the coressponding value of col3.
I need to group by Col1 but the value in col2 should be the min value in its group and col3 should the coreesponding value.


SELECT *
FROM Sample sb
WHERE pos = (SELECT MIN(pos)
FROM (SELECT pos,
Id,
Types
FROM Sample
GROUP BY Id,
Types))

I thought this would work but its erroring out.
Sorry I am a new user and do not know about messaging and am new to oracle too.
Re: MIN fUNCTION WITH GROUP BY [message #350897 is a reply to message #350872] Sat, 27 September 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Obviously you did not read the guide I mentionned or you did not find the "Preview Message" button, it at the left of "Submit Reply" which I know you found.

Also you did not read the functions I pointed you or did not try to use them.

In the end, it is highlyt suggested to post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: MIN fUNCTION WITH GROUP BY [message #350948 is a reply to message #350897] Sun, 28 September 2008 00:56 Go to previous messageGo to next message
manojkb
Messages: 16
Registered: March 2006
Location: Dubai
Junior Member
This should work.

SELECT col1, col2, col3 
FROM t1
WHERE (col1, col2) IN 
      (SELECT col1, MIN(col2)
       FROM t1 GROUP BY col1)
Re: MIN fUNCTION WITH GROUP BY [message #351186 is a reply to message #350948] Mon, 29 September 2008 13:33 Go to previous messageGo to next message
sammtron
Messages: 4
Registered: September 2008
Junior Member
Thank you very much.
It worked very nicely
Re: MIN fUNCTION WITH GROUP BY [message #351187 is a reply to message #350948] Mon, 29 September 2008 13:35 Go to previous message
sammtron
Messages: 4
Registered: September 2008
Junior Member
I am a newbie so such forum is are
a wonderful help to people like me.
Previous Topic: Reading explain plans
Next Topic: Another Trigger Issue
Goto Forum:
  


Current Time: Thu Dec 08 18:41:10 CST 2016

Total time taken to generate the page: 0.75877 seconds