ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

How to get max and sub max in group by sql query?

Table: 

 

IDSalesDate

/1/

1/1/

1            /3/

 

3          /8/

4          //

5          //

6          1//

7          /2/

6          /4/

7            //

 

required result

ID,max sales in this ID,max date in this ID

 

/3/

6  9//

 

user-image
تم إضافة السؤال من قبل Syed Sumair , IT Project Manager , Saudi National Bank
تاريخ النشر: 2016/03/20
Gayasuddin Mohammed
من قبل Gayasuddin Mohammed , Advocate , Practicing Law before High Court at Hyderabad

there are CUBE an ROLLUP kindup functions has been added in new versions and may be helpful for this requirement and not very sure as I'm not in touch with the subjects since long time.

otherwise logic development wise i can say  it is always better to write a small stored procedure to have the answer for such requirements

- easy to understand and better readable way is:

(1) select id, max(sales) into  #salesTable from idTable group by id

(2) select id, max(date)  into #dateTable from idTable group by id

(3)  select s.id, s.sales, d.date from #salesTable s, #dateTable d where s.id = d.id

will get you the desired result the same may be put into temp table for any reference.

I think it is of some help for you. Its been long time so please check the syntax's for their correctness.

Thanks.

mostafa mahmoud
من قبل mostafa mahmoud , Lead Business Intelligence Consultant , Fakeeh Care Group

the SQL MAX() can be used to find the maximum value of a column over each group.

baiju mohanan
من قبل baiju mohanan , BI Developer , OSN

To find maximum value in a group, we can use below query.

select  column1, max(column2) as maxvalue                            from table where = <condition>                                            group by column1.

to find sub max ( i assume it as2nd max value):

select  column1, max(column2) as maxvalue                             from table where column2 < (select max(column2) from table) group by column1.

                                                       

Parvez Hassan
من قبل Parvez Hassan , IT Incharge , Mahesh Edible Oil Industries Pvt. Ltd., Kota, Rajasthan-324007

table : customer

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+SELECT cust_city, cust_country,MAX( outstanding_amt ) FROM customer  GROUP BY cust_country, cust_cityorder by outstanding_amt descMethod(2)-SELECT a.Person, a.Group, a.Age FROM[TABLE_NAME] a INNERJOIN(SELECT`Group`, MAX(Age)AS oldest FROM[TABLE_NAME]GROUPBY`Group`) b ON a.Group= b.GroupAND a.Age = b.oldest

 

Hafiz Sameer Ahmad
من قبل Hafiz Sameer Ahmad , Labinat Consulting , Labinat Consulting

Max

select max(column) from table group by (columnn) where condition operator value

using the having clause in query we can get sub max like

select max(column) from table group by (columnn) where condition operator value

having (column)> maxrow

 

 

مستخدم محذوف‎
من قبل مستخدم محذوف‎

SELECT membership.mem_desc,membership.mem_max_rentals,membership_history.mem_type, membership_count, rank()overORDERBY membership_count DESCas ranky from(Select membership.mem_desc,membership.mem_max_rentals,membership_history.mem_type, count(membership_history.MEM_TYPE)as membership_count from membership_history JOIN membership ON membership.mem_type = membership_history.mem_type groupby(membership_history.mem_type,membership.mem_desc,membership.mem_max_rentals))WHERE ranky =1;

syed ali
من قبل syed ali , Oracle Applications Technical Lead , Rexall PharmaPlus

SELECT*FROM(SELECT*, ROW_NUMBER()OVER(PARTITIONBY alarmId, alarmUnit ORDERBY id DESC)AS rn FROM mytable ) q WHERE rn =1

المزيد من الأسئلة المماثلة