| Returning 2 values with Max function [message #370804] |
Thu, 10 February 2000 01:18  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
