Home » RDBMS Server » Server Administration » Returning 2 values with Max function
Returning 2 values with Max function [message #370804] Thu, 10 February 2000 01:18 Go to next message
Nell
Messages: 7
Registered: February 2000
Junior Member
Hi,
How do you return more than one value with the Max function? I have been trying to find out which month has the highest sales in the tblSales table. For example:
Month SalesAmount
Jan 100
Feb 200
Mar 300

I can do a select max(salesamount) from tblsales but I also want to know which month. How do I do that? I also tried:
select month, salesamount from tblsales where salesamount in
select max(salesamount) from tblsales

but it wouldnt work. Any advice? Thanks in advance.
Re: Returning 2 values with Max function [message #370805 is a reply to message #370804] Thu, 10 February 2000 05:32 Go to previous message
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
Junior Member
Hello,

Good Day!

Nice one...You can get it with this query...

Method I:
---------
SQL> Select Month_Name, Sales_Amt From Table_name where (Month_Name,Sales_Amt) in (Select Month_Name,Max(Sales_Amt) From Table_Name) Order By Month_Name Desc;

Method II:
----------
SQL> Select Month_Name, Sales_Amount From Table_Name where Sales_Amount in (Select Max(Sales_Amount) From Table_Name Group By Month_Name)

Method III:
-----------

Using Self Joins We can do that........

Method IV:
----------

Using 'SET OPERATORS', We can............

Regards

Atavur Rahaman S.A
Previous Topic: spatial and pl/sql
Next Topic: Re: Returning 2 values with Max function
Goto Forum:
  


Current Time: Thu Mar 28 18:19:40 CDT 2024