Home » SQL & PL/SQL » SQL & PL/SQL » Find max records in a group
Find max records in a group [message #416134] Thu, 30 July 2009 14:49 Go to next message
geogis
Messages: 2
Registered: July 2009
Location: WI
Junior Member
I'm attempting to find the max valued records in a group. I started with Xaprb's example and gone from there...
Table name = Fruits

Original sql:
select high.*
from Fruits as high
   left outer join fruits as higher on higher.type = high.type
      and higher.price < high.price
where higher.price is null;


Modified SQL attempt:
select high.*
from Fruits as high
   left outer join fruits as higher on ((higher.type = high.type)
   and (higher.in_stock = 'yes' and high.in_stock = 'yes'))
      and higher.price < high.price
where higher.price is null;


Example table
| type   | variety    | price |  in_stock
+--------+------------+-------+--------
| apple  | gala       |  2.35 | yes
| apple  | fuji       |  2.35 | yes
| apple  | limbertwig |  1.99 | yes
| apple  | delicious  |  3.50 | no 
| orange | valencia   |  3.59 | yes
| orange | navel      |  9.36 | yes
| pear   | bradford   |  6.05 | ues
| pear   | bartlett   |  2.14 | yes
| pear   | comise     |  3.99 | no
| cherry | bing       |  2.55 | yes
| cherry | chelan     |  6.33 | yes
Would like results of:
| type   | variety  | price |  in_stock
+--------+----------+-------+--------
| apple  | gala     |  2.35 | yes
| apple  | fuji     |  2.35 | yes
| orange | valencia |  3.59 | yes
| pear   | bradford |  6.05 | yes
| cherry | chelan   |  6.33 | yes

Thanks in advance

[EDITED by LF: applied [code] and [pre] tags]

[Updated on: Fri, 31 July 2009 00:37] by Moderator

Report message to a moderator

Re: Find max records in a group [message #416135 is a reply to message #416134] Thu, 30 July 2009 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/?SQ=ea1aa20288d414d672bb57aa50bca6e3&t=search&srch=max++group&btn_submit=Search&field=sub ject&forum_limiter=1&search_logic=AND&sort_order=DESC&author=

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Find max records in a group [message #416172 is a reply to message #416134] Thu, 30 July 2009 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Find max records in a group [message #416181 is a reply to message #416134] Fri, 31 July 2009 00:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Input:
| orange | valencia   |  3.59 | yes
| orange | navel      |  9.36 | yes

Output:
| orange | valencia |  3.59 | yes

Is that correct? How come is "valencia 3.59" > "valencia 9.36"?
Re: Find max records in a group [message #416273 is a reply to message #416181] Fri, 31 July 2009 07:35 Go to previous messageGo to next message
geogis
Messages: 2
Registered: July 2009
Location: WI
Junior Member
Sorry, the correct result for the orange vareity should have been.. | orange | navel | 9.36 | yes
I typed it incorrectly.

To the Moderators...thank you for the links / edits.
Re: Find max records in a group [message #416279 is a reply to message #416273] Fri, 31 July 2009 07:53 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 31 July 2009 06:58
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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel


Previous Topic: Pivot Query using connect by in 10g
Next Topic: display
Goto Forum:
  


Current Time: Fri Dec 09 21:45:55 CST 2016

Total time taken to generate the page: 0.09345 seconds