How to seperate and display the max value ?

Question

Hi Friends,

i ve the scenario like

The list of customer's was billed on 01-apr-2011

custname products value bill_date bill_no
ram Milk 25 01-apr-2011 25
ram Perfume 225 01-apr-2011 25
sam Egg 50 01-apr-2011 26
sam Medicine 125 01-apr-2011 26

now i wanna to extract the maximum billed value from above and display on my result like

custName products Max_sales billed billno
ram Perfume 225 01-apr-2011 25
sam Medicine 125 01-apr-2011 26

how to write code for these scenario?
do the need full

thanks

rocky

Khan Abrar Ahmed · Answer

Hi you can use below code DECLARE @t AS TABLE(custname VARCHAR(6),products VARCHAR(255), VALUE INT,bill_date DATE, bill_no INT) INSERT INTO @t VALUES ('ram' , 'Milk' , 25 , '01-apr-2011' , 25), ('ram', 'Perfume' , 225 , '01-apr-2011' , 25), ('sam' , 'Egg', 50 , '01-apr-2011' , 26), ('sam' , 'Medicine' , 125 , '01-apr-2011' , 26) select bill_no, products, VALUE as SALES, bill_date, custname from (select *, row_number() over(partition by bill_no order by VALUE desc) RowNumber FROM @t) t WHERE RowNumber=1

Hanook · Answer

select BillNo, products, Price as SALES, Billed_date, cust_det from (select *, row_number() over(partition by Billno order by price desc) RN FROM TableName ) t WHERE RN=1

Rocky Rocky · Answer

Above My scenario has Bill no products price Billed_date cust_det 25 Milk 25 01-Apr-2013 ram Perfume 225 01-Apr-2013 ram 26 EGG 50 01-Apr-2013 sam Medicine 125 01-Apr-2013 sam like lacks of records are there. My expecting O/p is: Bill no products SALES Billed_date cust_det 25 Perfume 225 01-Apr-2013 ram 26 Medicine 125 01-Apr-2013 sam Here i wanna display the maximum amount product only display on it to neglect other products how to that?

Kiran Kumar Talikoti · Answer

Hi Rocky , Check this Query Hope this Helps you select top 2 custname,products,value as Max_Sales,bill_date as Billed,bill_no as BillNo from customer order by value desc ;

Rocky Rocky · Answer

Thanks for ur reply But i need only to display the maximum value of each bills.

Masoud DaneshPour · Answer

you can do something like this : Select Top 2 * from YourTableName Order By Max_sales desc the "top 2" guy returns the first 2 row that has max value after ordering